I am new to relational databases and I was wondering if you guys could help me out with a quick question on the theory:
I am developing a message board system with three types of user - clients, staff and admin. They all have similar attributes, user id, password, real name, email address etc but depending on their type they have additional information, for example staff will have department information and administrators will have information on their areas of control. Should I put them all into the same table, with all the columns for all the types and leave NULL in the ones that don't apply to that user or should I have different tables for each type of user.
I don't like the idea of the NULL values but having different tables for each type of user would mean not being able to do things like 'SELECT comment.commentid, user.realname FROM user, comment WHERE user.username ="foo"' since the table user would be split over admin, client and staff.
I hope I've made this problem clear - it seems like the sort of thing that could be easily cleared up by thinking about it the right way with experience of RDBs.
Cheers,
Jos
I am developing a message board system with three types of user - clients, staff and admin. They all have similar attributes, user id, password, real name, email address etc but depending on their type they have additional information, for example staff will have department information and administrators will have information on their areas of control. Should I put them all into the same table, with all the columns for all the types and leave NULL in the ones that don't apply to that user or should I have different tables for each type of user.
I don't like the idea of the NULL values but having different tables for each type of user would mean not being able to do things like 'SELECT comment.commentid, user.realname FROM user, comment WHERE user.username ="foo"' since the table user would be split over admin, client and staff.
I hope I've made this problem clear - it seems like the sort of thing that could be easily cleared up by thinking about it the right way with experience of RDBs.
Cheers,
Jos