I'm working out the easiest method to represent the idea of "Special offers" during my database - these connect with "items available". Each special will offer you some kind of discount nonetheless they will be different meaning that certain will you need to be an easy cost reduction but others is going to be such things as "Buy x Get y Half Cost" or "30% off category x". I do not anticipate getting a lot more than around 10 special offers types... but needs change and I have to make certain I'm able to handle more when the need arises.
I'm attempting to develop an easy method to represent these during my DB however generate an income view it I'm either going to need to produce a table per special or I will need to have a special offers table with lots of posts which 90% from the values is going to be NULL. I intend to possess a SpecialType table that will group all special offers together by type e.g. "Discount","Buy x get y Free" and will also be employed to determine the company logic within the particular application layer. Furthermore I believed I possibly could go lower the road of getting 1 table per special type after which caching the outcomes (basically caching the renderable output per product) that may be up-to-date regularly, say every a few minutes, the aim of this is to do a lot of joins (EDIT - these would really most likely be UNIONS) but get it done fairly rarely because the information is a fantasy-time critical meaning that the 5 minute delay wouldn't kill anybody.
I'd appreciate any feedback regarding how to handle this case within the tidiest manner although keeping my database stabilized.
Cheers Take advantage of
Since an addition of the Special type will need a code change, and also you cannot reasonably anticipate all possible special types, I'd be comfy to produce a separate table for every kind of special, together with some type of master table, that will retain the special types, names, explanations, etc...
Utilizing a single table with a lot of inapplicable fields will often become an Inner Platform which is nearly always an overkill.
BTW, probably the most uncomfortable surprises during my existence, was after i discovered in regards to a new promotion that needed code/database changes from the TV commercial.