CODEWITHSUNDEEP
mysqldatabase-design
Arriane zafe
Arriane zafe

Reputation: 133

MYSQL: should i use 2 tables for 2 types of user with common and different field?

i have 2 type of users 1. employee. 2. employees friend.

both users have common fields.
1.first name
2.middle name
3.surname
4.cellphone number
5.telephone number
6.city address
7.email (serve as login)
8.password
9.registration date
10. update account date

and the employee friend user don't have this field
1.employee id
2.company name.
3.company branch
4.position

and they will be going to use same login form.

Upvotes: 1

Views: 256

Answers (3)

Luke94
Luke94

Reputation: 712

edited version (thanks to nawfal and Patrick James McDougle)

1st table (user) should contain field 1-10(first list) and a new field fk_employees (foreign key to our 2nd table, null if it is an employees_friend)

2nd table (employees) should contain field 1-4(second list)

Upvotes: 1

AnandPhadke
AnandPhadke

Reputation: 13506

what i suggest create 2 separate tables as users and user_friends with the fields mentioned above.

Now create a view with following query:

select employeeID,email,password from users
union
select friendID,email,password from user_friends

Query this view to get the login info in your application.

Upvotes: 0

Patrick James McDougle
Patrick James McDougle

Reputation: 2062

I think about this the same way I think about object oriented programming. If employee EXTENDS user then employee should be a user with extra information. I would have two tables.

Users (containing the common fields adding an id field perhaps) & Employees (with the 4 extra fields for employees and one field that references a unique identifier in the users table.)

More information about what I am proposing can be found here: http://www.agiledata.org/essays/mappingObjects.html#MappingInheritance

Upvotes: 0

Related Questions