Integrating tables to design a database in MySQL

Question

0.00/5 (No votes)

See more:

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 16-Dec-15 23:29pm

Member 12213239

Updated 17-Dec-15 0:12am

v2

Add a Solution

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.

CHill60 17-Dec-15 7:12am

If it's ok with you I'll post a summary of this as a solution?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2015-12-17T02:00:00

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'