集成表以在MySQL中设计数据库 [英] Integrating tables to design a database in MySQL
问题描述
我正在使用Azure上的CakePHP和MySQL开发一个Web应用程序。我有两个表 - 经理和客户。我使用用户表来存储身份验证数据，如用户名和密码。经理可以拥有很多客户。表格如下：
经理
id（ primary key ）
name
customers
id（ primary key ）
manager_id（ foreign key ）
name
users
id
username
password
userType（manager / customer）
方案是用户将使用注册表格注册，他/她必须是经理或客户。如果用户是经理，数据将保存在管理器表中，如果是客户，则数据将保存在客户表中。我的问题是如何关联或设计用户表以将其与经理和客户表集成。有人可以帮我吗？
如上所述。
一种选择是拥有桌子与此相似...
<前一行=SQL> 创建 表 person
（
PersonId int identity （ 1 ， 1 ） primary key ，
PersonName varchar （max），
userType char ，
manager_id int FOREIGN KEY REFERENCES person（PersonId） NULL
）
< span class =code-keyword> create table AuthorisedUsers
（
AuthId int identity （ 1 ， 1 ） primary key ，
用户名 varchar （max），
密码 varchar （max），
P_id int FOREIGN KEY REFERENCES person（PersonId） NOT NULL
）
其中userType对于客户来说是'C'而''是'对于经理。
请注意，密码不应以纯文本格式存储 - 我建议您阅读初学者指导安全的方式o f存储密码 [ ^ ]（或关于此主题的其他Code Project文章之一）
那么你可以做像- 插入没有经理的客户
INSERT INTO person VALUES （' 客户'，' C'， null ）
- 插入经理
插入 INTO 人 VALUES （' 经理'，' M'， null ）
- insert a以经理人为经理的客户
INSERT INTO person < span class =code-keyword> VALUES （' 客户2'，' C'， 2 ）
- 让我们再来一个
INSERT INTO person VALUES （' 客户3'，' C' ， 2 ）然后你可以做
- 找到所有拥有' 经理' as 他们的经理
SELECT * FROM person WHERE manager_id =（SELECT personid 来自人WHERE personname = ' A经理'）并获取
PersonId PersonName UserType Manager_id
3客户2 C 2
4客户3 C 2
然后你继续说客户可能是他们自己的经理，例如- 插入也是他们自己的经理的客户
- 首次作为客户插入
INSERT INTO person VALUES （' 客户4'，' C'， null ）
- 然后查找记录ID并更新manager_id
更新人 SET manager_id =（< span class =code-keyword> SELECT PersonId FROM person WHERE PersonName = ' 客户4'）
WHERE PersonName = ' Customer 4'
现在这引发了一个有趣的问题。考虑列出所有经理人。直观地说，你会写SELECT * FROM person WHERE userType ='M'但是客户4没有出现在该列表中，因为它们既是客户又是经理。你可以写
SELECT * FROM person WHERE PersonId IN
（SELECT manager_id FROM person WHERE manager_id不为null）
Yuk！而且它会使userType变得多余。
如果一个人可以拥有多个角色（超过1个），我们需要重新访问数据库模式：创建 table person
（
PersonId int identity （ 1 ， 1 ） primary key ，
PersonName varchar （max），
userType char ，- - 删除此
manager_id int FOREIGN KEY REFERENCES person（PersonId） NULL
）
介绍一个ew表，它将成为每个人的角色列表create table UserRoles
（
roleId int identity （ 1 ， 1 ） primary key ，
ShortKey char ，
P_id int FOREIGN KEY REFERENCES person（PersonId） NOT NULL
）
插入一些数据对于我们已经拥有的人将插入 UserRoles值
（' C'， 1 ）， - 客户
（' M'， 2 ）， - 经理
（' C'， 3 ）， - 客户 2
（' C'， 4 ）， - Customer 3
（' C'， 5 ）， - 客户 4 as a Customer
（' M'， 5 ） - 客户 4 as a Manager
现在你可以找到（所有！）像这样的经理选择 * 来自人P
内部 join UserRoles U on P.PersonId = U.P_id
其中 U.ShortKey = ' M'
I am developing a web application using CakePHP and MySQL on Azure. I have two tables - managers and customers. I am using a users table to store authentication data like usernames and password. a manager can have many customers. the tables look like following -
managers
id (primary key)
name
customers
id (primary key)
manager_id (foreign key)
name
users
id
username
password
userType (manager/customer)
the scenario is the user will signup using a registration form where he/she has to be a manager or a customer. if the user is manager, the data will be saved in manager table, if it is customer, then the data will be saved in customer table. my question is how to relate or design the users table to integrate it with manager and customer tables. Could anyone help me please ?
As discussed above.
One option would be to have tables similar to this ...
create table person ( PersonId int identity(1,1) primary key, PersonName varchar(max), userType char, manager_id int FOREIGN KEY REFERENCES person(PersonId) NULL ) create table AuthorisedUsers ( AuthId int identity(1,1) primary key, username varchar(max), password varchar(max), P_id int FOREIGN KEY REFERENCES person(PersonId) NOT NULL )
WhereuserTypewill be 'C' for a customer and 'M' for a manager.
Note that password should not be stored in plain text - I recommend you read Beginners guide to a secure way of storing passwords[^] (or one of the other Code Project articles on the subject)
Then you can do things like-- insert a customer with no manager INSERT INTO person VALUES ('A Customer', 'C', null) -- insert a manager INSERT INTO person VALUES ('A Manager', 'M', null) -- insert a customer who has 'A Manager' as their manager INSERT INTO person VALUES ('Customer 2', 'C', 2) -- And let's have another one INSERT INTO person VALUES ('Customer 3', 'C', 2)Then you can do
--Find all of the Customers who have 'A Manager' as their manager SELECT * FROM person WHERE manager_id = (SELECT personid from person WHERE personname = 'A Manager')and get
PersonId PersonName UserType Manager_id 3 Customer 2 C 2 4 Customer 3 C 2
You then went on to say that a Customer could be their own manager e.g.-- insert a customer who is also their own Manager -- first insert as a customer INSERT INTO person VALUES('Customer 4','C', null) -- then find that record id and update the manager_id UPDATE person SET manager_id = (SELECT PersonId FROM person WHERE PersonName = 'Customer 4') WHERE PersonName = 'Customer 4'
This now raises an interesting problem. Consider "list all persons who are managers". Intuitively you would writeSELECT * FROM person WHERE userType = 'M'But Customer 4 doesn't appear in that list because they are BOTH a customer AND a manager. You could write
SELECT * FROM person WHERE PersonId IN (SELECT manager_id FROM person WHERE manager_id is not null)
Yuk! And it sort of makes userType redundant.
If a person can have many roles (more than 1) we need to revisit the database schema:create table person ( PersonId int identity(1,1) primary key, PersonName varchar(max),userType char,-- remove this manager_id int FOREIGN KEY REFERENCES person(PersonId) NULL )
Introduce a new table which will be the "list" of roles for each personcreate table UserRoles ( roleId int identity(1,1) primary key, ShortKey char, P_id int FOREIGN KEY REFERENCES person(PersonId) NOT NULL )
Insert some data for the persons we already haveinsert into UserRoles values ('C', 1), -- A Customer ('M', 2), -- A Manager ('C', 3), -- Customer 2 ('C', 4), -- Customer 3 ('C', 5), -- Customer 4 as a Customer ('M', 5) -- Customer 4 as a Manager
Now you can find (all!) the managers like thisselect * from person P inner join UserRoles U on P.PersonId = U.P_id where U.ShortKey = 'M'
这篇关于集成表以在MySQL中设计数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
