Take the 2-minute tour ×
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
1  
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
add comment

1 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...

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
show 4 more comments

Your Answer

 
discard

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.