Best way for database design

Question

I am trying to find the best solution for my database.

What i have

Offer

DateOffer                       the date the offer is made
RateId                          foreign key to table with price/tax rates
Hours                           ammount of hours worked
Buissnes (boolean)              is it for a buissnes 
                                (yes tax = WorkTaxBuissnes no = WorkTax)
ExtraHours (boolean)            if extra hours calculate with ExtraHourRate

Rates

RateId
WorkTax 
WorkTaxBuissnes
NormalHourRate
ExtraHourRate

You can see that i need to look for the taxes in the other table, is it smarter to get the taxes on index and add the values to the table Offer so i can do everything from the table offer.

I could change Buissnes (offer) to tax, and extrahours (offer) to WorkTax.

The advantage is i dont need to use any join or if check (extrahours yes no ? buissness yes no?).

The disadvantage is i cant to see if it were extra hours, or if i calculated buissnes tax (cause they change) or i could keep them as extra fields.

Can some one give me advice.

Answer 1

My final solution.

I kept the database as it was in my example so i wont have any double data, (2 times the same tax)

But i added a View wich functions as a table but is in fact a query wich runs on the other tables and returns the values wich can be used like a table like select * from myview

Answer 2

If Rates is an update-able entity then prevent to keep a copy of them in Offers table.

Consider we have rate-1 (record) in rates table, assume we are going to have 10 Offers (offer-1 ... offer-10) relating to this rate-1.
After insertion of offers we decide to update rate-1 values.
Scenario 1: add the values to the table Offer (de-normalization )
When inserting offer-1, offer-2, ... offer-10 we will keep redundant data from rates inside offers.
After insertion, when update of rate-1 happens,we need to update offer-1 .. offer-10
At lest 11 updates is needed (1 for rate-1 and 10 for offers)
Advantage:

• Calculation of offer costs based on rates will be done fast, no need to extra joins.

Disadvantage:

• We redundant same data in 10 records.
• We forced 10 extra updates.
• If any update fails we will have inconsistent data

Scenario 2: Keep rate related values inside Rate table (Normalization)

Disadvantage:

• Calculation of offer costs is based on rates table data, we need an extra join to fetch rates. less performance is gained. that will cost performance loss.

Advantage:

• We keep rate data in one place, we gained consistency.
• When update of rate happens we kept integrity of our data.

A standard database design guidance is that the designer should first create a fully normalized dsign then selective denormalization can be performed for performance reasons.

Normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency.
Denormalization is the process of attempting to optimize the read performance of a database by adding redundant data or by grouping data.

Hint: Programmers building their first database are often primarily concerned with performance. There’s no question that performance is important. A bad design can easily result in database operations that take ten to a hundred times as much time as they should.

Answer 3

Maybe i'm wrong, but in this simple case i see your second option much more efficient even if there are very limited cases of Rates.

Storage Requirements (with very limited number of rates):

1. Date + 2 x 4 bytes + 2 x 1 bytes (if your database supports tinyints) = Date + 10 bytes. (i don't add the storage of the rates because i consider they are limited)
2. Date + 5 x 4 bytes (if you can save the rate id) = Date + 20 bytes.

So the space due to this will be less than doubled, but queries will be lot faster in the case there are extra hours involved. Plus you can execute queries based on the rate values directly in one table.

If the space is not an issue, go the second way no doubt. And if there are many rate cases same thing.