Our company provides a number of services to the clients (e.g., website hosting, technical support, custom programming, etc...). There is a page on our website that lists all available services as well as their corresponding prices. It was static data, but my boss wants everything drawn from the database rather.
You will find about 100 services listed. Only a couple of them, however, possess a non number value for "cost" (particularly, the strings "ISA" and "cost + 8%" - I truly have no idea what they are designed to mean, so don't request me).
I'd hate to create the "cost" column a varchar just due to both of these entries. My current approach is to produce a special "cost_display" area, that is either blank or consists of the written text to show instead of the cost. This solution feels an excessive amount of just like a dirty hack though (it might needlessly complicate the queries), so it is possible to better solution?
Take into account that this column is really a cost displayed towards the customer that may contain anything.
You would be inviting grief by trying to really make it a number column. You are already battling with two non-conforming values, and tomorrow your manager may want more...
- Cost ON APPLICATION!
- Give Us A Call FOR The modern SPECIAL!!
You get the drift.
If you actually need a number column then refer to it as internalPrice or something like that, and set your number constraints on that column rather.
After I have needed to do that kind of factor previously I made use of:
Price Unit Display 10.00 item null 100.00 box null null null "Call for Pricing"
Cost could be decimal datatype (any exact number, not float or real), unit and display could be some form of string data type.
Then used the situation statement to show the cost with either the cost per unit or even the display. Also put a constraint or trigger around the display column to ensure that it should be null unless of course cost is null. A constraint or trigger also needs to need a value in unit if cost isn't null.
By doing this you are able to calcuate prices to have an order where possible and then leave them out once the cost isn't specified but display both. I'd also include a busness rule to make certain the entire couldn't be totalled before the demand prices was resolved (which you'd also need to have a method to place the special prices towards the order particulars as opposed to just pull in the cost table).
Am I Going To be adding these values? Am I Going To be sorting by cost? Should i have to transform with other currency values?
Am I Going To you need to be exhibiting this value on the web site?
If this sounds like only a laundry list and never employed for computation the easiest option would be to keep cost like a string (varchar).
Possibly make use of a 'type' indicator within the primary table, with one child table permitting number cost and the other with character values. This can be combined into one table, however i generally avoid that. You might make use of an intermediate link table having a quantity should you ever wish to base cost on quantity bought.
Plenty of options:
- All prices saved as varchars
- Prices saved numerically and additional cost_display area that overrides the amount if populated
- Prices saved numberically and additional cost_display area for display reasons populated by hand or on trigger when number cost is up-to-date (duplication of information also it could get free from sync - yuk)
- Store special situation negative prices that map to special situations (simply yuk!!)
- varchar cost, prefix key area to some table of accessible prefixes ('cost +', ...), suffix key area to some table of accessible suffixes, type area answer to a listing of types for that value in cost ('$', '%', 'description'). Helpful if you would have to write complex queries against prices later on.
I'd most likely choose 2 like a practical solution, as well as an extension of 5 basically needed something very general for any generic prices system.