Storing data in records question 1

[bcooler]

OK, I have a general strategy question.

I am used to creating records that store related tables' primary keys. For example, when I put a customer name in an order form, I store their key, not the name (243, not John Smith). I believe this is the standard way to store info. It allows me to have access to all of John Smith's data, not just a name (and many other reasons).

However, my brother wants me to make a database for him and I am concerned with using the above method is all instances. For example, if a particular customer has a discount associated with their account (% stored in the customer table), I can make an order form that takes the existing percentage and apply it to the order (everything is good at this point).

Now, here's the problem. Let's say next month this same customer has their discount adjusted in the customer table (in an attempt to give a better price on future sales). New orders will still work just as before, but older completed orders will now seem to have the current discount % instead of the historical discount given at time of sale (due to storing the customer's autonumber and extracting the associated discount; a big problem).

I can have the Order table store the actual discount value in the record instead of only storing the customer primary key (and looking up the discount). This will store the actual discount for all time in this record.


MY QUESTION:


I've always been told to store primary keys and do not store redundant information. However, this solution I described seems to disregard that strategy (discount code is now in the customer table AND the order table). I don't know what hidden "grenades" I'm about to get myself into if I go forward with the above solution.

Anyone willing to hold my hand?

 

[PHV]

Either store the actual discount in the Orders table, or have a table of customer's discount with a date column.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bcooler]

That sounds good. I hadn't thought of the second idea. These solutions won't get me into trouble later, correct (reports, queries, etc)?

 

[MazeWorX]

Nope

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[bcooler]

Thanks so much!

 

[MajP]

You may want two date fields in your discount table.

TblCustomerDiscounts
customerID (foreign key to customer table)
discountStartDate
dicountEndDate
discountRate

This allows you to do a join where customerID = customerID and orderDate between discountStartDate and discountEndDate

And if storing the rate in the order table this would allow you to have a pull down for the correct rate based on the order date.

 

[MazeWorX]

I prefer the discount written to the order. This way a new discount% can be changed on the customers table at any time without any issue to your orders table

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[bcooler]

Thanks guys. Maybe a little of both is best for me. I'll store the actual discount on the order table, but instead of the original discount information being a stagnant field in the customer's table, I will link a discount table (with dates) to the customer. That way, my brother can leisurely set up the discount codes ahead of time (instead of trying to do it all after work before the customers start calling in the morning).