0

I am trying to design a database for my app and webservice, Lets say the table for View properties is something like this:

survey

So View has prop_id, x, y, width, height, bg_color, txt_color

And after a while i need to add a new column to this table like:

text_size, has_shadow ...etc

The type of property is dynamic, and the number of properties is dynamic as well.

so how can we design for such kind of extendable databases?

MBH
  • 16,271
  • 19
  • 99
  • 149
  • 1
    best bet is to create a table with id, prop, value and just add new row for whatever new property you add.. then you can pivot the result if you want – JamieD77 Mar 31 '16 at 15:00
  • Duplicate? http://stackoverflow.com/questions/6768074/extendable-database-schema-how-to-store-the-extendable-attribute-values?rq=1 – Neville Kuyt Mar 31 '16 at 15:55

1 Answers1

2

When you need a new property, you add a column to your table.

I'm sure that people will suggest that you try to use the EAV (Entity-Attribute-Value) model to accommodate these kinds of database changes, but that's a huge mistake, IMO. There are very, very few instances where that anti-pattern is called for, and this isn't one of them.

If your database needs to now track "text_size" then presumably your application will require changes that deal with this text size. You're going to have to change your application and/or database as it is, so you buy nothing (other than headaches) by using the EAV model.

If you Google EAV then you can find a lot more information on it and why it's a bad idea, but in a nutshell it makes querying your database more difficult and less performant, and makes maintenance more difficult, not easier.

Tom H
  • 46,766
  • 14
  • 87
  • 128
  • so every time new attribute came up somehow, i should add new column to database? isnt it expensive operations as well ? – MBH Mar 31 '16 at 19:37
  • 2
    You would need to define, "expensive". Expensive in what way? Adding a column to a table is simply a metadata change, which is almost instantaneous. How often do you expect to be adding new attributes? If you add an attribute, don't you already need to make sure that your application can accommodate it? – Tom H Mar 31 '16 at 20:16
  • I imagined that adding attribute would be expensive since it will be added and inserted as 0 or default value for the whole table. So i got the idea while writing the app, the customer started to add new stuff, and i was like what if he asked after we publish... thats why i asked this question – MBH Mar 31 '16 at 20:21
  • 2
    If you have default values that you need to propagate to all of the rows in the table **and** the table is **very** large then there is a cost to that. So, you run the update at night and it takes 20 seconds to run (or whatever amount of time). If you're worried about requirements changing during the development the that's part of development - you shouldn't have to contort your database to handle that. Add the column and move on. Using EAV isn't going to make it any easier in the short term and will make things much harder in the long term. – Tom H Mar 31 '16 at 20:30