Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
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 '12 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 '12 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...

share|improve this answer
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 '12 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. –  FrustratedWithFormsDesigner Feb 14 '12 at 20:32
OK good deal. <extra chars> –  JNK Feb 14 '12 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 '12 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. –  FrustratedWithFormsDesigner Feb 14 '12 at 22:34

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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