Table structure/schema for spreadsheet-like web app (ex: Google Docs)?

Question

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

• JNK: 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.
• Kgosser: Thanks for the link, JNK. Great insight! EAV is new to me so this will be a good read for me.

Answer 1

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

Spreadsheet
 spreadsheet_id (unique key)
 name
 num_rows
 num_cols
column_types
 type_id (unique key)
 type_name
spreadsheet_rows
 spreadsheet_row_id (unique key)
 spreadsheet_id (refers to spreadsheet.spreadsheet_id)
 row_seq_num (for on-screen row-ordering)
 row_name
spreadsheet_cols
 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)
 column_name
spreadsheet_cells
 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...

• JNK: This will be messy since the Num_Rows and Num_Columns values will need to change every time a cell is added/deleted.
• FrustratedWithFormsDesigner: @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.
• JNK: OK good deal. <extra chars>
• Kgosser: 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.
• FrustratedWithFormsDesigner: @Kgosser: I supposes if this is too generic, you could rename some things, and remove the pieces you don't need/want.