0

Possible Duplicate:
What are design patterns to support custom fields in an application?

I use SQLite to store my data. I have several columns in the table like id, title, description and notes. Now I need to extend my data model and keep other 1, other 2, ... , other N fields in database. I want the user to add them dynamically.

I just don't know how to handle the case because the amount of columns in the table is fixed. What's approach for this case?

Community
  • 1
  • 1
Eugene
  • 59,186
  • 91
  • 226
  • 333

2 Answers2

1

A possible approach is to use Attribute-Value-Pair pattern where you can have separate table with following schema:

  • id (from your original table)
  • attributeName (that can be your other 1, other 2, etc).
  • attributeValue

You can further normalize this approach, by creating separate table for your attributes, for example with following schema:

  • Id
  • Name
  • DataType

Then your original table would become

  • id
  • attributeId
  • attributeValue
Sebastian K
  • 6,235
  • 1
  • 43
  • 67
  • 1
    EAV is only appropriate when you have a large number of possible values, but a small number of actual values (patient symptoms in a doctor's office, for example), and there are significant disadvantages to this approach. See http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model for more information. – Robert Harvey Dec 27 '12 at 21:58
  • 1
    I agree with @Sebastian approach but in the last more normalized case the original entity disappear you don't have any more a list of things, now you have a list of attributes, I know is a little more complicated but I prefer maintain the original entity table, add an second table Extended_Properties_List and a relational table that cross Entity_id, Property_id and value. – HMarioD Dec 27 '12 at 22:21
0

SQLite supports ALTER TABLE ADD COLUMN syntax. More info here.

SQLite doesn't support all DDL features. See here for those features in the SQL92 specification that are not supported.

Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
  • This would result in empty columns for some rows which don't have additional fields. – Eugene Dec 27 '12 at 21:56
  • You can't specify columns on a per-row basis. If you want per-row attached values, look into [EAV](http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model). – Robert Harvey Dec 27 '12 at 21:57