CODEWITHSUNDEEP
databaseexceldatabase-design
Hidalgo
Hidalgo

Reputation: 765

modeling an excel table (dynamic columns and fixed rows)

please help me finding the correct structure of a database table that allow to store the data of an excel table that looks like this:

enter image description here

first I tried to store rows and columns headers in tables:

rows(id_row, label)
columns(id_col, label)

then I created a table for storing values like this:

mytable(id_tbl, #id_row, #id_col, value, date_add)

but I realized that this solution need more processing to select and manipulate (deleting, edition) and maybe it will affect the performance of my code

and since I have a fixed rows, I decided to do it like this:

columns(id_col,label)
mytable(id_tbl, id_col, date_add, row_1, row_2, row_3)

so in order to store an instance of my excel table, I will need 1 row in the mytable table for each column

So is this the right way to do it?

Upvotes: 0

Views: 468

Answers (1)

andy holaday
andy holaday

Reputation: 2302

A database table definition declares its columns, not the rows. Depending on what you are modeling and how you are going to use this table I would suggest one of these approaches.

A well-normalized approach would be something like this:

mytable(unique_id, entity_label, value_label, column_value, [other entity attributes e.g. date_added])

Some decoding: entity_label is what you have in Excel Column A, value_label is what you have in Excel Row 1. Your data stored in this manner would look something like this:

unique_id  entity_label  value_label   column_value
1          row_1         col_1         9 
2          row_1         col_2         19 
3          row_1         col_3         29 
4          row_2         col_1         50

and so on.

This stores one and only one value (column_value) per database row, and is a flexible and extensible approach. For example, since entity_label and value_label are identified on each database row, it will effortlessly handle an arbitrary number of value_labels per entity (Excel "col_nn") and overall data points. It is a simple matter to sum all the column_values for an entity_label (as you seem to be doing in Excel Column E). Deleting, adding, and updating an entity or specific value within an entity are all possible and simple.

On the other hand it is possible your entity model always has three values per entity. Much as you have presented your source data, a table definition mimicking this approach could be this:

mytable(unique_id, entity_label, column_value_1, column_value_2, column_value_3, [other entity attributes e.g. date_added])

The major disadvantage of this approach is the lack of flexibility. For example, if the number of value_labels per entity ever changes (say, increases from 3 to 4), you might find yourself editing a lot of code just to take into account the entity's new column.

A mantra I like to repeat is "rows are cheap, columns are expensive." Keep this in mind as you design your database tables.

Upvotes: 2

Related Questions