I'm a front-end developer/designer who is trying to branch out and learn more about the back-end world. I've chosen Python, Django, and Postgres as my starting place. My goal is to build a personal web app, and use that as my catalyst for learning. It's been really fun.

I'm at the stage where I'm designing the data model. I would normally tinker and figure things out, but in this case I'm curious what is possible.

A good analogy for my app's data model is a spreadsheet. The user can create a "spreadsheet", then determine "columns", and subsequently the data type per column.

So, as a pure example, a new sheet has the following columns:

Name (text)
Cost (number / currency)
Date (time stamp
Frequency (number)
Notes (text)

Then, "rows" can be added where each entry has values for name/cost/date/frequency/notes, as well as some meta data like who created the row, at what time, etc.

I'm unsure what is the best schema to accomplish this example. Below is the best idea I came up with. I'm wondering if anyone would be willing to comment on whether this is the best approach, or give feedback on a different direction.

enter image description here

  • 2
    Something like this may be best with a NOSQL solution. I'm guessing the google one uses BigTable. Variable structure is hard to do in a relational DB, and it's actually kind of an anti-pattern. EAV is a four-letter word for a lot of folks.
    – JNK
    Feb 14, 2012 at 19:51
  • Thanks for the link, JNK. Great insight! EAV is new to me so this will be a good read for me.
    – Kgosser
    Feb 14, 2012 at 22:19

1 Answer 1


I've never attempted something like this, but maybe a schema such as this would work:

    spreadsheet_id (unique key)

    type_id (unique key)

    spreadsheet_row_id (unique key)
    spreadsheet_id (refers to spreadsheet.spreadsheet_id)
    row_seq_num (for on-screen row-ordering)

    spreadsheet_col_id (unique key)
    spreadsheet_id (refers to spreadsheet.spreadsheet_id)
    col_seq_num (for on-screen column-ordering)
    column_type_id (refers to column_types.type_id)

    cell_id (unique key)
    spreadsheet_id (refers to spreadsheet.spreadsheet_id)
    row_id (refers to spreadsheet_rows.spreadsheet_row_id)
    col_id (refers to spreadsheet_cols.spreadsheet_col_id)
    cell_value (holds the actual value!)

Of course you'd only create records in spreadsheet_cells for non-null values...

  • This will be messy since the Num_Rows and Num_Columns values will need to change every time a cell is added/deleted.
    – JNK
    Feb 14, 2012 at 20:06
  • @JNK: I intended for num_rows and num_cols to represent the number of rows and columns that will be displayed, not populated. When the user creates a new 5x5 spreadsheet, num_rows and num_cols will both be 5, even though there is nothing in any cell. These are only changed when the user explicitly adds/removes a row/column. Feb 14, 2012 at 20:32
  • OK good deal. <extra chars>
    – JNK
    Feb 14, 2012 at 20:34
  • Interesting insight, Thanks for the feedback. For what it's worth, with the specific, small personal app I'm building there wouldn't be the concept of "updating" or "editing" the spreadsheet. Rather, think of it more like a Google Form where each new submission simply adds another entry to the "spreadsheet", or form. So, i.e. there isn't much of a spreedsheet UI as there is data concept.
    – Kgosser
    Feb 14, 2012 at 22:29
  • @Kgosser: I supposes if this is too generic, you could rename some things, and remove the pieces you don't need/want. Feb 14, 2012 at 22:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.