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 am new to database design and have started developing my first schema.

Some of my tables (e.g. user_profile) I am struggling to come up with an exhaustive list of fields for the table. A particular use case could be that a user wants to add an additional field to their profile e.g. favourite color.

I have read a few articles that say the way to capture this use case is to include an 'other' field to the database. This field then maybe contains a json string of additional fields.

Is this method the best practice way to add additional fields to a table? It seems somewhat hacky to me. The obvious con is that the additional fields will not be as accessible when the database runs a query.

Note: I can see that there may be two different scenarios here. One where users have a personalised list of fields (in which case i think the method above will be the only way). Another where over time I realise all users would benefit from having an additional field in their profile. Comments on both scenarios would be appreciated.

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

You can't design a schema to cope with every possible eventuality, so don't try. Couple of options:

  • Design for what you know now. Revisit and modify when new requirements emerge.
  • Use an EAV model to extend the entities which require a flexible property bag of additional fields. You can use a traditional normalised model for the common/base attributes and EAV for expansion.

For a use case such as yours there's a good argument for reading the user profile attributes once per session and caching until exit. This avoids the major EAV caveat, crappy performance.

share|improve this answer
    
thanks. Have read the link. Seems like EAV should only be used as a last resort. Considering the case where I decide that all users should have a 'favourite_color' field. Will there be any issues if i just add the field to my table? (new to dbs so sorry if it's a noob question) –  Gaz_Edge Feb 19 '13 at 10:26
    
The problem with extra columns is that it is always easiest at the time to just tack it on, but then you end up after some time with 30 odd columns added that shouldn't really be there. Bloating indexes, wasting diskspace with many nulls. And by the time you notice it is all in use so very hard to normalise properly afterwards. –  JamesRyan Feb 19 '13 at 10:43
2  
EAV is good as long as the data has no meaning to you, but then why are you collecting it? One strategy might be to use that to see what fields are used and then convert commonly chosen fields into a proper normalised structure later. –  JamesRyan Feb 19 '13 at 10:51
    
@JamesRyan I disagree on your first point. I'm not a fan of EAV but there are valid use cases. Your last suggestion is something I've proposed in an answer here (somewhere) previously, it can certainly help. –  Mark Storey-Smith Feb 19 '13 at 11:04
1  
EAV is the worst choice for design unles the data changes are totally unpreodicatble. It is far better to adjust the schema when you need new fields. EAV are performance killers. –  HLGEM Feb 19 '13 at 16:02
show 4 more comments

Where info is personalised it should be normalised into linked tables that only join to rows where that optional column has been added. Also if you might be adding many columns or long data it is common to have a core user table with extended info tables mapped on a one to one basis. These methods allow for smaller indexes more specialised to each query scenario.

Generally, good normalisation and matching tables well to real world entities rather than theoretical ones gives the most adaptable and future proof design.

share|improve this answer
add comment

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.