I'm try to figure out the best scheme for the users table for my software. I'm trying to separate the main data and auxiliary data into 2 table called users and user_meta for the sake of extensibility (something like what WordPress has done).
Now I need to figure out which data to put into which table. Here is the basic data a user might have.
user_id
email
password
user_type -> int
register_date
phone
name
image -> url
description -> text (might contain html)
... so many other possible attributes for a user which is specific to my software
It would be great if you could help me decide which kind of data belongs to which table.
Best Answer
The name
user_metafor a "meta_table" does not seem to match up with your description. As maru alluded, metadata is data that describes the data. The data dictionary already stores this metadata including the name of the column, the column type, how large the column is, and depending on the platform may even contain a description of the column.What is sounds like you want is an auxiliary users table so that a single users table can be used for all applications, with application specific user information in this auxiliary table. If you only have one application and don't have immediate plans to create another, then I suggest you put all the fields in the users table and only split it when/if it becomes necessary.
If on the other hand you will have multiple applications, each with distinct user attributes, then such a table may be useful. I suggest including the application name in the name of the auxiliary table. So, if you have an abc application and an xyz application you would have three tables --
Users,UsersABCandUsersXYZ.As to which columns should go in which table, put anything that could conceivably be used by another application in the general
Userstable, so that application ABC never has to joinUsersXYZ. All the columns you listed look to me like they should go in theUserstable.