Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

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.

share|improve this answer

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
...
share|improve this answer
    
Thanks for your answer, but unfortunately I didn't really get what you're trying to say. Can you be more specific? –  Hamed Momeni Jan 25 '13 at 10:21
    
the user_meta table contains information that DESCRIBES the user table. while the user table contains the ACTUAL data being described by the meta. –  maru Jan 25 '13 at 12:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.