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

Perhaps use a 'type' indicator in the main table, with one child table allowing numeric price and another with character values. These could be combined into one table, but I generally avoid that. You could also use an intermediate link table with a quantity if you ever want to base price on quantity purchased.

Answer 2

Lots of choices:

1. All prices stored as varchars
2. Prices stored numerically and extra price_display field that overrides the number if populated
3. Prices stored numberically and extra price_display field for display purposes populated manually or on trigger when numeric price is updated (duplication of data and it could get out of sync - yuk)
4. Store special case negative prices that map to special situations (simply yuk!!)
5. varchar price, prefix key field to a table of available prefixes ('cost +', ...), suffix key field to a table of available suffixes, type field key to a list of types for the value in price ('$', '%', 'description'). Useful if you'd need to write complex queries against prices in the future.

I'd probably go for 2 as a pragmatic solution, and an extension of 5 if I needed something very general for a generic pricing system.

Answer 3

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 4

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).

Answer 5

If this is the extent of your data model, then a varchar field is fine. Your normal prices - decimal as they may be - are probably useless for calculations anyway. How do you compare $10/GB for "data transfer" and $25/month for "domain hosting"?

Your data model for this particular project isn't about pricing, but about displaying pricing. Design with that in mind.

Of course - if you're storing the price a particular customer paid for a particular project, or trying to figure out what to charge a particular customer - then you have a different (more complex) domain. And you'll need a different model to support that.

Answer 6

In that at least one of the alternate prices have a number involved, what about a Price column, a price type? The normal entries could be a number for the dollar value and type 'dollar', and the other could be 8 and 'PercentOverCost' and null and 'ISA' (for the Price and PriceType column).

You should probably have a PriceType table to validate and PriceTypeID if you go this route.

This would allow other types of pricing to be added in the future (unit pricing, foriegn currancy), give you a number, and also make it easier to know what type pricing you are dealing with..

Answer 7

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 8

http://books.google.co.in/books?id=0f9oLxovqIMC&pg=PA352&lpg=PA352&dq=How+do+you+handle+%E2%80%9Cspecial-case%E2%80%9D+data+when+modeling+a+database%3F&source=bl&ots=KxN9eRgO9q&sig=NqWPvxceNJPoyZzVS4AUtE-FF5c&hl=en&ei=V3RlSpDtI4bVkAWkzbHNDg&sa=X&oi=book_result&ct=result&resnum=3