How do you handle "special-case" data when modeling a database?

Question

Our organization provides a variety of services to our clients (e.g., web hosting, tech support, custom programming, etc...). There's a page on our website that lists all available services and their corresponding prices. This was static data, but my boss wants it all pulled from a database instead.

There are about 100 services listed. Only two of them, however, have a non numeric value for "price" (specifically, the strings "ISA" and "cost + 8%" - I really don't know what they're supposed to mean, so don't ask me).

I'd hate to make the "price" column a varchar just because of these two listings. My current approach is to create a special "price_display" field, which is either blank or contains the text to display in place of the price. This solution feels too much like a dirty hack though (it would needlessly complicate the queries), so is there a better solution?

Answer 1

Consider that this column is a price displayed to the customer that can contain anything.

You'd be inviting grief if you try to make it a numeric column. You're already struggling with two non-conforming values, and tomorrow your boss might want more...

• PRICE ON APPLICATION!
• CALL US FOR TODAYS SPECIAL!!

You get the idea.

If you really need a numeric column then call it internalPrice or something, and put your numeric constraints on that column instead.

Answer 2

When I have had to do this sort of thing in the past I used:

Price   Unit   Display
10.00   item   null
100.00  box    null
null    null   "Call for Pricing"

Price would be decimal datatype (any exact numeric, not float or real), unit and display would be some type of string data type.

Then used the case statement to display the price with either the price per unit or the display. Also put a constraint or trigger on the display column so that it must be null unless price is null. A constraint or trigger should also require a value in unit if price is not null.

This way you can calcuate prices for an order where possible and leave them out when the price is not specified but display both. I'd also put in a busness rule to make sure the total could not be totalled until the call for pricing was resolved (which you would also have to have a way to insert the special pricing to the order details rather than just pull from the price table).

Answer 3

Ask yourself...

Will I be adding these values? Will I be sorting by price? Will I need to convert to other currency values?

OR

Will I just be displaying this value on a web page?

If this is just a laundry list and not used for computation the simplest solution is to store price as a string (varchar).