0

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?

|improve this question
  • 1
    This is not clear at all – Arj Dec 8 '11 at 2:19
  • 1
    @ericW, the title of the question is not reflected in the body of the question. Please adjust either/both. Thanks. – NoChance Dec 8 '11 at 2:32
6

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.

|improve this answer
  • Does there any link i can get useful info for Normalize data? – ericW Dec 8 '11 at 3:29
  • 3
    Yes, I hear Google has a few. – Morons Dec 8 '11 at 3:40
  • All kidding a side, Data normalization is not simple topic (it's not overly complex either) But it's complex enough that i would recommend you find something in your native language. – Morons Dec 8 '11 at 3:43
  • en.wikipedia.org/wiki/Database_normalization is a good start. – Kyle Hodgson Dec 8 '11 at 4:32
  • 3
    "DO NOT MODEL YOUR DATA BASED ON HOW YOU WANT YOUR CODE TO CONSUME IT!" -- I don't think this can be stressed enough. Databases tend to be some of the most long-lived IT assets. It's not unheard of that a database has survived multiple generations of hardware, operating systems, programmling languages, applications and programmers. That's one of the fundamental differences between database design and program design: programs are designed such that they can be easily changed and extended, databases are designed such that they never need to change. – Jörg W Mittag Dec 8 '11 at 5:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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