13

I have a 4 types of users and each have specific data, but they also share commun data, like username, password ..

My first thought is to create a main users table with user_type column. Then when querying user data i can just first select their user_type and then depending on the output run a different query to grab "user type" specific data. I am not fond of this as i wish i could grab all user related data with one query and ideally using Foreign Keys.

Second idea is to not have a user_type column in the users table and instead use foreign key that from a specific user type table will point to a row the main users table. I like that a bit better though i guess i will have to run N queries, where N is the number of user type every time i need to grab user data.

Are there any other options ? What would be the good practice in such a case ?

Many thanks

Improve this question
3
  • If I understand you, you just need to use a JOIN clause in your SQL statement. Something like SELECT * FROM users U LEFT JOIN user_details D ON U.id = D.user_id WHERE U.id = ? Commented Dec 6, 2012 at 18:17
  • Can a user be of more than one type at a time? Commented Dec 6, 2012 at 18:29
  • @Catcall no in this scenario a user can only have one type, though i am curious of what are the possibilities in either case Commented Dec 6, 2012 at 18:34

3 Answers 3

Reset to default
26

Your case looks like an instance of class/subclass.

There are two classic ways to design SQL tables to deal with subclasses. Each has advantages and disadvantages.

One way is called "Single Table Inheritance". In this design there is just one table for all types of users. If a given column doesn't pertain to a given row, the intersection is left NULL. A column can be added to indicate the user type.

Another way is called "Class Table Inheritance". This is much like the answer Nanego gave, with a few minor changes. There is one table for users, with all the common data, and a id field. There is one table for each subclass, with data that pertains to that subclass. The id field is often set up as a copy of the id field in the matching row back in the users table. This way the subclass key can do double duty, acting as both a primary key and as a foreign key referencing the user table. This last technique is called "Shared Primary Key". It requires a little programming at insert time, but it's well worth it. It enforces the one-to one nature of the relationship, and it speeds up the necessary joins.

You can look up all three of these designs as tags in SO or as articles out on the web.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for dropping those terms, googled them and already learned a lot
7

Although it's more efficient use of disk space, the problem with splitting into separate tables is that you effectively require conditional joins - joining to the user-type specific table based on the user_type. This is a pain to code as SQL, and these days who cares about disk space?

The better option is to have one user table with enough columns to store information about any user type, knowing that some columns won't be used for some user types. The "inefficiency" of having unused columns will more than be compensated for in execution speed and query simplicity.

It's also easily extendible, in case you get another user type - it's far easier to add columns than it is to add tables, and as you added more user types, the requirement for new columns would diminish (there just aren't that many different things about a user you need to store)

Improve this answer

3 Comments

I hate to upvote this since it just feels sloppy, but it really is the truth.
I do agree that would make the code a lot cleaner to avoid joins but I would love to have tables that match the PHP classes, It just would feel more elegant
Also i see one downside to this is that the table loose integrity as i'd have to make all field nullable which can lead to error in the backend code.
1

My way to do it would have been to create one table "Person" with the common fields, and one table for each type of user with a foreign key "person_id".

In your request, you just have to join two tables with the foreign_key in order to get all data for one type of user.

How many types of user do you have ?

Improve this answer

1 Comment

right now 4 but i wanna keep that flexible.. The second solution i mentioned is actually what you are suggesting.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.