Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Just keep them as one table and use NULL appropriately. –  Phil Oct 21 '12 at 11:04
add comment

1 Answer 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...
share|improve this answer
    
Just personal preference, but I'd stick with one table with a "user_type" column. –  Phil Oct 22 '12 at 15:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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