What to have in the main table and what to have in meta_table?

Question

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.

Answer 1

The name user_meta for 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, UsersABC and UsersXYZ.

As to which columns should go in which table, put anything that could conceivably be used by another application in the general Users table, so that application ABC never has to join UsersXYZ. All the columns you listed look to me like they should go in the Users table.

Answer 2

the user_meta table should contain details about the fields that will be in the user table.

for example, user_meta will have rows for each field in the user table

user_meta
---------
user_field_name | data_type  | size  | extra params  
---------------------------------------------------
user_name       | varchar    | 220   | primary key  
password        |varchar     |220    |not null

while the user table will have rows containing every field stated in the user_meta using the example above, the user table will have look like this

users
-----
user_name |password  
-------------------
user1     |password1  
user2     |password2
...