How to efficiently create a large SQLite database with variable numbers of fields in rows?

Question

I've fired up SQLite Database Browser and found Blob, Int and Text data types. There's also a box to name your own type but I'm expecting those to not be widely supported in SQLite.

If I have records with variable numbers of fields (each structured as a combination of fields itself, eg int & text) how would I put them in a table? I'm guessing that I would need to make another table for these. Is there no more elegant solution? Especially with regard to creating the database manually using an SQLite database manager.

Look into http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model. — Kermit, Jun 25 '12 at 17:01

score 2 · Answer 1 · answered Jun 25 '12 at 18:21

2

You can kinda store dynamic field names in a two table setup.

Create two tables...header and details. Header is going to hold the primary ID along with any information that is common to all rows of the record. Details is going to hold the ID from the header, the 'column name', and the column value.

Create table header (id int, desc character varying)

Create table details (id int, header_id int, column_name character varying, column_value character varying)

Get the idea with the structure? It's known as a name value pair relation (entity / attribute) and can store any number of 'columns' for a single header record...you then need to pivot (or build together) the details table to read the information in it (can be done as views)

answered Jun 25 '12 at 18:21

Twelfth

7,070
3
26
34

so the second table incurs the overhead of needing its very own primary key? Being used to native structs I'd prolly been hoping an index would have sufficed,for free, as it were. – John Jun 25 '12 at 18:29
You are correct, it doesn't need it's own key.... id, column_name would also work. My personal preference is to usually leave a single int column as a primary, but it's optional. – Twelfth Jun 25 '12 at 19:53
heh, sorry...meant you are correct header_id, column_name would work for the ID. – Twelfth Jun 25 '12 at 20:12

score 1 · Answer 2 · answered Jun 25 '12 at 16:59

This would be a typical one-to-many relationship. Yes, make another table of those fields, with an additional column that is the ID of the record it relies to.

If those fields don't contain much data (they are numbers or tags), in SQLite it's sometimes better to store them as comma (or other symbol) separated values in a TEXT column.

How to efficiently create a large SQLite database with variable numbers of fields in rows?

2 Answers2