Jump to content
Larry Ullman's Book Forums

Prices And Normalization


Recommended Posts

Hi guys,

 

Another quick normalization/database planning question:

 

In the book, Larry re-records the price of each sale in the order_contents and orders tables (instead using a FK to the product) in case the price of the product changes in the future.

 

My situation is somewhat different: I am working on a ticketing system. Each event ticket consists of multiple "Price Points". A Price Point says that on day x at time x the price of this ticket becomes $x. So the ticket price increases as the event becomes closer.

 

Now Price Points cannot be edited. To overwrite a Price Point, the user creates a new one. So in my order_contents table I am simply going to use a FK to the price point purchased, since the price of that price point can never change. Simple enough.

 

Here is my question: This company is very promo-code happy. They make upward of 50 discount codes per ticket! To keep the database nice and clean (and hopefully well normalized), which would be the smarter option? -

 

a: Make promo codes also un-editable, and just include in the orders table a FK to the promo code used. The con is I will have thousands and thousands of codes which can never be deleted.

b: Store for each order the promo code name and price used. It is a little redundant, but maybe should be done anyway (like Larry did) so I don't have to keep all those thousands of promo codes in the database for all time. (Keep in mind that I expect most promo codes to be used at least once, so I am recording lots of extra data this way.)

 

 

Many thanks.

Link to comment
Share on other sites

I'm not quite sure I understand the situation fully, but what about having a promo_codes table with all the possible promo codes, and then applying the promo codes as necessary?

 

I should probably clarify my idea a bit. Let's imagine there are the following promo codes:

 

promo_code_10   10
promo_code_30   30
promo_code_50   50

 

Where the number on the right is the percentage discount. Then you could add the ID of these promo codes as foreign keys to the tickets table, in order to mark the current promo code that applies to a ticket. Therefore, if promo_code_50 were currently applied to a ticket, you'd just multiple the ticket cost by promo-code-percentage/100.

 

Anyway, I really don't know if I even understood your situation properly, so please let me know if I'm way off.

Link to comment
Share on other sites

 Share

×
×
  • Create New...