Click here to Skip to main content
15,849,996 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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 -

SQL
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 ?
Posted
Updated 17-Dec-15 1:12am
v2
Comments
CHill60 17-Dec-15 6:06am    
I'm not sure why you need the managers and customers tables when you have userType on your users table. If you add manager_id onto users as a foreign to itself then you have all the information you need in a single table
Member 12213239 17-Dec-15 6:10am    
a manager can have many customers. in that case, how to define this relation in one users table with usertype column ?
Member 12213239 17-Dec-15 6:23am    
you mean i only need users and manager tables ? customers table is not necessary ?
CHill60 17-Dec-15 6:27am    
I mean you only need the users table. a manager is just a type of user and a customer is just a type of user.
As to your earlier comment I suggested adding another column "manager_id" - thus each customer would have this set so you can identify the customers for each manager.

Alternatively you have a "person" table containing both managers and customers (including userType and manager_id) with a second table for authentication details ... this latter table should include a foreign key to the person table but not duplicate any information held on that table.
Member 12213239 17-Dec-15 7:08am    
thank you for your answers. i need to add another issue here. a manager can have many customers, but it is also possible that a customer is without manager or its own manager. in that case, which of your above options would be better. single users table(including manager-id and usertype) or person (with userType and manager_id) and a users table (with username and password). thank you so much again for clarifying my confusions.

1 solution

As discussed above.

One option would be to have tables similar to this ...
SQL
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
)

Where userType will 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
SQL
-- 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
C#
--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.
SQL
-- 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 write
SELECT * 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:
SQL
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 person
SQL
create 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 have
C#
insert 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 this
SQL
select * from person P
inner join UserRoles U on P.PersonId = U.P_id
where U.ShortKey = 'M'
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900