When I do database table design, I often wonder if I should try to make the table as simple as possible, or if I should make the object as clear as possible.
The requirement is to store all month of the year's salesTon and gross profit.
I design from the object.The object has fields year, month, salesTon and gross profit. For this situation when I need save data to the database, it will save 12 rows(one row one month). This object has a relationship with another object (customer). so when save it will save n(customer)*12 rows per one time. It will do a huge insert operation. I'm afraid that tihs will make the db slow.
Another design is to just use one object. One object to save the whole year – no need to split the object in to months. But in the object will has 12*2's fields like(month1_sales_ton, month1_gross_profit, month2_sales_ton, month2_gross_profit…….), this situation make save least rows, base on n(customer) per time.
But since the second situation doesn't have month, I can't write the common method when I want to search one year's one month gross profit which accept year, month parameters. Maybe I should just write the method to just pass year and get whole a year object? i don't know.
So which principle should I follow when I design the table?
Best Answer
You need to Normalize your data properly. You are not helping anybody with these summary tables. You are NOT Speeding anything up, YOU ARE NOT SAVING ANY DISK SPACE!*
I feel like a broken record saying this, but :
DO NOT MODEL YOUR DATA BASED ON HOW YOU WANT YOUR CODE TO CONSUME IT!
*You need to fully understand the rules of normalization before knowing when to break them (and you clearly don't). I can guarantee, in the long run, that de-normalizing will slow you down, take up more space, and inundate you with data integrity issues.
Oh Yea, The Most important thing when modeling your data is the actual Entity (and there data points) and how they relate to other Entities. This is why data models are generally synonymous with Entity relationship diagrams.