0

I would like some advice on the best database structure for my system.

I have different kinds of users with different access levels, and different information of course. I created a users table with the following fields:

id
student_number
password
first_name
last_name
address
email
mothers_name
mothers_occupation
fathers_name
fathers_occupation
religion
civil_status

The problem is that some users just don't need all those fields. The only user type that needs all those are the students users. The student users have a different 'environment' / module that sends a request to an api, that then sends back a json response.

Other fields, for other type of users, are not applicable to them, for example, an admin, he/she mustn't have those other fields (I could use dummy data, but I am looking for a cleaner approach).

I was thinking I can just create tables like this:

Users table

id
username
password

UserInfo table:

user_id (FK)
first_name
last_name
...

What do you think? Any examples/erd of big users database?

  • Just keep them as one table and use NULL appropriately. – Philᵀᴹ Oct 21 '12 at 11:04
1
Users.

id, student_number, password, first_name, last_name, address, email, mothers_name,  
mothers_occupation, fathers_name, fathers_occupation, religion, civil_status

Roles
id, name

UserRoleMembership
user_id, role_id

If the problem is that you actually do want to validate some of those fields - but only for some users, I would use inheritance:

users
id, username, password

student_user
id,
user_id,
last_name required,
first_name required,
etc.

admin_user
id,
user_id,
admin_info_fields...
  • Just personal preference, but I'd stick with one table with a "user_type" column. – Philᵀᴹ Oct 22 '12 at 15:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.