Question: I am developing a MySQL db for a user list, and I am trying to determine the most efficient way to design it.

My issue comes in that there are 3 types of users: "general", "normal", and "super". General and normal users differ only in the values of certain columns, so the schema to store them is identical. However, super users have at least 4 extra columns of info that needs to be stored.

In addition, each user needs a unique `user_id` for reference from other parts of the site.

So, I can keep all 3 users in the same table, but then I would have a lot of `NULL` values stored for the general and normal user rows.

Or, I can split the users into 2 tables: general/normal and super. This would get rid of the abundance of `NULLs`, but would require a lot more work to keep track of the user\_ids and ensure they are unique, as I would have to handle that in my PHP instead of just doing a `SERIAL` column in the single table solution above.

Which solution is more efficient in terms of memory usage and performance?

Or is there another, better solution I am not seeing?

Thanks!

Answer: 
If each user needs a unique id, then you have the answer to your question: You want one `users` table with a `UserId` column. Often, that column would be an auto-incremented integer primary key column -- a good approach to the implementation.

What to do about the other columns? This depends on a number different factors, which are not well explained in your question.

You *can* store all the columns in the same table. In fact, you could then implement views so you can see users of only one type. However, if a lot of the extra columns are fixed-width (such as numbers) then space is still allocated. Whether or not this is an issue is simply a question of the nature of the columns and the relative numbers of different users.

You can also store the extra columns for each type in its own table. This would have a foreign key relationship to the original table, using the `UserId`. If both these keys are primary keys, then the `join`s should be very fast.

There are more exotic possibilities as well. If the columns do not need to be indexed, then MySQL 5.7 has support for JSON, so they could all go into one column. Some databases (particularly columnar-oriented ones) allows "vertical partitioning" where different columns in a single table are stored in separate allocation units. MySQL does not (yet) support vertical partitioning.
If you want to have this type of schema. try to create a relation

like:

tb\_user > user\_id , user\_type\_id(int)   

tb\_user\_type > user\_type\_id(int) , type\_name

this way you will have just 2 tables and if the type is not set you can set a default value to a user.