I want an idea about my database design. I've about 5 fields for fundamental information of user, for example title, email, gender etc. Then I wish to have about 5 fields for optional information for example messenger id's. And 1 optional text area for information on user. Should i create just one tabel with all of fields altogether or i ought to create separate table for that 5 optional fields to be able to avoid redundancy etc? Thanks.
I'll stick to just one table.
Adding another table would only makes thins more difficult and you'll only gain really little disk space.
And That I really do not observe how this is often redundant by any means )
I believe that you ought to certainly stick to one table. Since information is pertinent to some user and don't reflect every other logical model (as an article, blog publish or such), you are able to securely keep everything in one location, even when they're optional.
I'd create only one table for further fields. Although not with 5 fields but an overseas key regards to base table and key/pair value info. Something similar to:
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 may want an
additional_info table to carry possible valid values for added info: messenger, extra email, whatever. But that's your decision. I would not bother.
It is dependent on the number of individuals will be getting all that optional information and whether you intend on adding more fields. If you feel you are likely to increase the fields later on, it may be helpful to maneuver that information to some meta table while using EAV pattern : http://en.wikipedia.org/wiki/Entity-attribute-value_model
So, if you are unsure, your table could be like
User : id, name, email, gender, field1, field2 User_Meta : id, user_id, attribute, value
While using user_id area inside your meta table, you are able to link it for your user table and add as numerous sparsely used optional fields as you would like.
Note : This takes care of Only when there are many sparsely populated optional fields. Otherwise get it in a single area
I recommend utilizing a single table with this. Databases are extremely proficient at optimizing away space for empty posts.
Splitting this table out into several tables is a good example of vertical partitioning as well as in this situation will probably be a situation of premature optimisation. However, this method could be helpful if you have posts that you simply only have to query a few of the time, eg. large binary blobs.