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.

For application, which has few (5-10) settings at the moment but will have a lot more (up to 100) in the future which would be better approach:

  1. to add new columns to settings table every time new setting is introduced
  2. to have settings table as key/value storage

Consider that application might have millions of instances (which all run on the same db and use the same table for settings), DB might be sharded. DB is relational, i.e. either MySQL or T-SQL.

As a developer I would prefer the 2nd variant, so I could scale the application without changing the DB schema and add/remove settings at will. As far as I understand having several million records in a single table shouldn't be an issue if the index is clustered on application instance. Are there any drawbacks that I'm not aware of?

And what about 1st variant? Are there any big benefits? And what about the number of columns: is there any theoretical limit of columns that table can have? What happened if I had a table with 1000 (10 000, 1000 000) columns? Would that be slow?

share|improve this question

2 Answers 2

up vote 4 down vote accepted

Option 2 is known as "EAV" or Entity-Attribute-Value

  • not relational
  • no DB level constraints
  • requires contortions to read the data unless a simple list

But, it depends what you mean by "settings". If you have a few 1000 rows that are not objects and don't require constraints then, yes, use this pattern. This is what SQL Server does with sys.configurations

If you are trying to have a "flexible schema" that can store anything, then simply don't. It will end in tears. Also see EAV questions here on DBA.SE

Note that "extra columns" (option 1) allows you to define defaults and datatype-safety, whereas "lack of row" (option 2) requires a default value to be stored in code and everything is a string in the database

"It depends"

share|improve this answer
    
It's pretty much like sys.configurations. Not trying to have flexible schema - I think that's what document oriented databases are for. Good point about defaults and data type, thanks! –  valentinas May 7 '12 at 11:45

Neither.

Instead, do the following:

Plan A:

  1. Decide which fields really need indexes on them. No, you don't need indexes on all fields.
  2. Put the rest of the columns, plus any future columns, in a "hash" or "associative" array in your programming language. Marshal this into JSON.
  3. Compress the JSON and store it into a MEDIUMBLOB column of your table.
  4. Zero effort to add a new column (or have an optional column, or deprecate a column, etc)

Features:

  • Efficient storage (compressed blob is much smaller than separate columns)
  • No need for ALTER TABLE ADD COLUMN.
  • Search capability (Remember, I had you keep those columns out.)

Plan B: MariaDB 5.3+ and its Dynamic Columns: http://kb.askmonty.org/en/dynamic-columns

share|improve this answer
1  
If I could choose another platform (not standart MySQL/TSQL), then I would probably go with some document database, ravenDB, google App Engine or similar, but that's not the case. As for plan A: do you have any references that would confirm what you're saying? For me this sounds like a really bad practice - the data that I will have would probably fall to nvarchar(100) type and placing that in blob (and loosing ability to query over it) sounds like a really bad idea. –  valentinas May 17 '12 at 22:51

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.