about database design

I need some idea about my database design. I have about 5 fields for basic information of user, such as name, email, gender etc. Then I want to have about 5 fields for optional information such as messenger id's. And 1 optional text field for info about user. Should i c开发者_运维问答reate only one tabel with all fields all together or i should create separate table for the 5 optional fields in order to avoid redundancy etc? Thanks.

---

I'll stick with only one table.

Adding another table would only makes thins more complicated and you will only gain really little disk space.

And I really don't see how this can be redundant in any way ;)

---

I think that you should definately stick with one table. Since all information is relevant to a user and do not reflect any other logical model (like an article, blog post or such), you can safely keep everything in one place, even if they are optional.

---

I would create only one table for additional fields. But not with 5 fields but a foreign key relation to base table and key/pair value info. Something like:

create table users (
    user_id integer,
    name varchar(200),
    -- the rest of the fields
)

create table users_additional_info (
    user_id integer references users(user_id) not null,
    ai_type varchar(10) not null, -- type of additional info: messenger, extra email
    ai_value varchar(200) not null
)

Eventually you might want an additional_info table to hold possible valid values for extra info: messenger, extra email, whatever. But that is up to you. I wouldn't bother.

---

It depends on how many people will be having all of that optional information and whether you plan on adding more fields. If you think you're going to add more fields in the future, it might be useful to move that information to a meta table using the EAV pattern : http://en.wikipedia.org/wiki/Entity-attribute-value_model

So, if you're unsure, your table would be like

User : id, name, email, gender, field1, field2
User_Meta : id, user_id, attribute, value

Using the user_id field in your meta table, you can link it to your user table and add as many sparsely used optional fields as you want.

Note : This pays off ONLY if you have many sparsely populated optional fields. Otherwise have it in one field

---

I would suggest using a single table for this. Databases are very good at optimizing away space for empty columns.

Splitting this table out into two or more tables is an example of vertical partitioning and in this case is likely to be a case of premature optimization. However, this technique can be useful when you have columns that you only need to query some of the time, eg. large binary blobs.