How to add user customized data to database?

I am trying to design a sqlite database that will store notes. Each of these notes will have common fields like title, due date, details, priority, and completed.

In addition though, I would like to add data for more specialized notes like price for shopping list items and author/publisher data for books.

I also want to have a few general purpose fields that users can fill with whatever text data they want.

How can I design my database table in this case?

I could just have a field for each piece of开发者_JAVA技巧 data for every note, but that would waste a lot of fields and I'd like to have other options and suggestions.

---

There are several standard approaches you could use for solving this situation.

1. You could create separate tables for each kind of note, copying over the common columns in each case. this would be easy but it would make it difficult to query over all notes.

2. You could create one large table with many columns and some kind of type field which would let you know which type of note it is (and therefore which subset of columns to use)

   CREATE TABLE NOTE ( ID int PRIMARY KEY, NOTE_TYPE int, DUEDATE datetime, ...more common fields, price NUMBER NULL, author VARCHAR(100) NULL,.. more specific fields)

3. you could break your tables up into a inheritance relationship something like this:

   CREATE TABLE NOTE ( ID int PRIMARY KEY, NOTE_TYPE int, DUEDATE datetime, ...more common fields);

   CREATE TABLE SHOPPINGLITITEM (ID int PRIMARY KEY, NOTE_ID int FORIENKEY NOTE.ID, price number ... more shopping list item fields)

Option 1 would be easy to implement but would involve lots of mostly redundant table definitions.

Option 2 would be easy to create and easy to write queries on but would be space inefficient

And option 3 would be more space efficient and less redundant but would possibly have slower queries because of all the foreign keys.

This is the typical set of trade-offs for modeling these kinds of relationships in SQL, any of these solutions could be appropriate for use case depending non your performance requirements.

---

You could create something like a custom_field table. It gets pretty messy once you start to normalize.

So you have your note table with it's common fields.

Now add:

dynamic_note_field

id     label    
1      publisher
2      color
3      size

dynamic_note_field_data

id     dynamic_note_field_id   value
1      1                       Penguin
2      1                       Marvel
3      2                       Red

Finally, you can relate instances of your data with the fields they use through

note_dynamic_note_field_data

note_id dynamic_note_field_data_id
1       1
1       3
2       2

So now we've said: note_id 1 has two additional fields. The first one has a value "Penguin" and represents a publisher. The second one has a value of "Red" and represents a color.

So what's the point of normalizing it this far?

1. You're not wasting space adding fields to every item (you relate a note with it's additional dynamic field via the m2m table).
2. You're not storing redundant labels (you may continue to store redundant data however as the same publisher is likely to appear many times... this aspect is extremely subjective. If you want rich data about your publishers you typically want to take the step of turning them into their own entity rather than an ad-hoc string. Be careful when making this leap because it adds an extra level of hairiness to the db. Evaluate the use case accordingly.

The dynamic_note_field acts as your data definition. If you're interested in answering a question such as "what are the additional fields I've created" this lets you do it easily without searching all of your dynamic_note_field_data. Eventually, you might add extra info to this table such as a type field. I like to create this separation off the bat, but that might be a violation of the YAGNI principle in your case.

Disadvantages:

It's not too bad to search for all notes that have a publisher, where that publisher is "Penguin".

What's tricky is something like "Find any note with a value of 'Penguin' in any field". You don't know up front which field's your searching. At this point you're better off with a separate index that's generated alongside your normalized db data which acts as the point of truth. Again, the nice thing about normalization is that you maintain the data in a very lossless, non-destructive state.

---

For data you want to store but does not have to be searchable, another option is to serialize it to/from JSON and store it in a TEXT column. This gives you arbitrary structure, but you cannot readily query against those values.

Yet another option is to dump SQLite and go with an object database. I seem to recall there are one or two working for Android. I have not tried any of these, however.

---

Just create a small table which contains the common fields of all your notes. Then a table for each class of special notes you have, that that contains all the extra fiels plus a reference on your first table.

For each note you will enter, you create a row in your main table (that contains the common fields) and a row in your extra table that contains the extra fields, and a reference to the row in your main table. Then you will just have to make a join in you request.

With this solution : 1)you have a safe design (can't access fields that are not part of your note) 2)your db will be optimized