May 18, 2012 at 8:00 am
Hi,
Looking for some advice on the best way to design a table or couple of tables to quickly get answers for reporting.
We sell products that can be installed in something. I'm going to use bikes for my example. So our current tables are
Bikes
* BikeID
* etc
Products
* ProductID
* Product Classification(s), etc
Orders
* OrderID
* OrderDate
* BikeID
* ProductID
* etc
We also sell ancillaries to our main product but you can only have one active main product in your bike at a time. You can have the main product removed and have it replaced with a similar but different product or the same product (different serial number). You can also just have the main product removed.
So the order history can look something like this:
[font="Courier New"]
OrderID OrderDate BikeID ProductID
Order01 01/01/2012 Bike01 Install01
Order04 02/15/2012 Bike01 Remove01
Order05 02/15/2012 Bike01 Install02
Order07 02/17/2012 Bike01 Ancillary01
Order08 02/17/2012 Bike02 Install02
Order09 02/19/2012 Bike02 Remove02
Order10 02/28/2012 Bike03 Install01[/font]
I need to be able to get answers to such questions as:
- What was the original install date for a bike?
- What's the current product in a bike?
- Does the bike have an active product?
- If there was a removal, what product was it for and what was it replaced with?
- What ancillaries are in there?
Approach 1:
I was thinking that a Bike / Product mapping table similar to what I show above for the order history (or even leverage the existing order history tables) and use of windowing functions would get me my answers but wasn't sure about performance.
Approach 2:
My other though was to have a table that looks something like this updated on a daily basis:
[font="Courier New"]
BikeID Original Install Original Product Current Install Current Product Active
Bike01 01/01/2012 Install01 02/15/2012 Install02 Y
Bike02 02/17/2012 Install02 N
Bike03 02/28/2012 Install03 Y[/font]
I'm leaning toward the 1st approach but I'm looking for opinions and options on the best way to store this data.
Thanks.
-Tom
May 18, 2012 at 8:17 am
i really would avoid approach 2 - this might make querying easier for now, but someone will change the reports later as "new requirements are added" and maybe this table won't feed those new requriements.
there is also the risk that the table may become out of date or if an error occurs you would have to "rebuild it"
also it increases complexity - the risk of a regression defect in later development increases with extra complexity - (e.g you change the bikes table and you forget to change the summary table - or orders suddenly come in pairs because of a new requirement.)
i read somewhere recently that one of the many points of normalisation in table design is to "avoid forcing any specific querying style or pattern" - a denormalised report table kind of forces your query style (although i admit that it would be an argument that would be easy to dismiss/contradict - i'm not asking people to shoot that out of the water , i already admit it's quite tenuous)
the query activities you are describing seem like normal simple queries that can easily be acheived if you stick to a good 3rd normal form design
MVDBA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply