December 7, 2010 at 6:38 pm
Hi Folks,
I am designing a database and have come across a weird business case, which is this:
The company has product ranges. There are two types of ranges: those where all the products in the range are unique to that range and then other ranges where the SAME product is offered in different ranges, and the only difference between each range is an attribute such as weight of the material the product is made of (ie. so all the products in that range are the same material).
I know there are lots of ways to model this and I've tried a few, believe me, but still cannot decide on the best approach.
Let's take some example data, which is carpets.
Let's say you have range called Tundra, and that range has 3 weights: 40oz, 50oz, 60oz.
Let's say that Tundra comes in 40 different colours and all colours are the same price within each weight band.
Should I create a range Tundra, and then 120 products? So the 40 colours are all there for each range, and against each product I stored the weight and price? It happens that NEW weights can be introduced, so therefore this could easily end up as 200 products...a maitenance nightmare when you consider that there is absolutely nothing to distinguish between these products (if they stop selling Tundra in green, they stop selling it in all weights simultaneously).
RANGE
RangeID (pk)
RangeName
PRODUCT
ProductID (pk)
RangeID (fk)
ProductName
ProductPrice
ProductWeight
ProductColour
Should I create 3 ranges for Tundra, with a Weight column to distinguish between them and then create the 40 products in the products table with a many-to-many between range and product?
RANGE
RangeID (pk)
RangeName
RangeWeight
RangePrice
RANGEPRODUCT
RangeID (fk)
ProductID (fk)
PRODUCT
ProductID (pk)
ProductName
ProductColour
ProductPrice
This would SEEM to solve the problem nicely, and those products that only belong to one range are catered for. EXCEPT the problem now is that these products each have a unique price and so I have two price fields - one at the range level and one at the product level...thus complicating the price lookup code!
I have almost been banging my head against the wall on this one and I really do not want to solve it using an EAV (entity attribute value) solution, so does anyone have any ideas?
Thanks,
Richard
December 7, 2010 at 6:56 pm
I thought I should perhaps add the most obvious alternative that I could think of, but which I'm somewhat unsure of:
RANGE
RangeID (pk)
RangeName
IsRangeOnePrice = a flag to help application logic know when to look at the rangeprice table?
PRODUCT
ProductID (pk)
RangeID (fk)
ProductName
ProductColour
ProductPrice <- would be null for those products that belong to a "weighted" range (ie. where IsRangeOnePrice=true)
RANGEPRICE
RangeID (pk, fk)
RangeWeight (pk) -> could be provided as a fk from reference table
RangePrice
So for those ranges that have different weights, and all the products are the same price, we have an extra table for that info. Those ranges whose products are priced individually and we don't care about weights are still 1 to many. So this instance, Tundra would exist in the RANGE table and in the RANGEPRICE table 3 times for each weight, with a different price for each one. This table would need a compound key of {RangeID, RangeWeight}.
December 8, 2010 at 12:51 am
Richard McSharry (12/7/2010)
Let's say you have range called Tundra, and that range has 3 weights: 40oz, 50oz, 60oz.Let's say that Tundra comes in 40 different colours and all colours are the same price within each weight band.
A bit confused where ProductName comes from and what it is based on. But going by the data above i would do:
RANGE
RangeID (pk)
RangeName
RANGECOLOR
RangeID (pk, fk)
ColorID (pk, fk)
RANGEWEIGHT
RangeID (pk, fk)
Weight (pk)
Price
COLOR
ColorID (pk)
Name
Why no table for Weight as color? Well color feels more static then weight. So a lookup table for valid colors felt needed. For weight that didnt feel quite as usefull. But thats a guess from my part.
As far as ProductName goes. Isnt that based on Color and Weight? If you truely have unique ProductNames for each product then your first exampel i think would work best. But if say its based on weight. Say the 40oz is Standard and 50oz is Premium and so on. Then you could simply add a column to RangeWeight that have this information. I would add it to this table even if i had decided to add a Weight table. Since there is a good chance that one might not want the same name for every range that uses a specific weight.
You have vastly more columns then i have but. There is really no information as to why and how these would be used. And what they are based on, so slightly hard to give advice on that.
/T
December 8, 2010 at 5:07 am
tommyh (12/8/2010)
A bit confused where ProductName comes from and what it is based on.
Sorry, but ProductName is usually some made up rubbish they give to each product, like Sierra, Stateside Blue, Cornflower Peach - it can literally be anything. This is why you also need colour as a field, so that we can actually record the nearest real colour that the carpet is so you can filter on colour. Note that each range has a name, each product has a name and each product also has a colour. A range does not have a colour, only a weight and a name.
Thanks for your proposal tommy, but as you can see it won't work.
The fundamental problem is that there are three types of ranges:
1. A range with no weight, all products in the range are a different price.
2. A range with no weight, all products in the range are the same price
3. A range with differing weights, each sub-range being a different price, all product within each sub-range are the same price
(1) and (2) are solvable with the simple one-to-many and storing a range price and a product price. (3) is what really causes the hurt! :crying:
December 8, 2010 at 6:15 am
Ah i see. Yeah mine wont work. Think i would go for something like your second post then. Not much choice as i see it.
/T
December 8, 2010 at 7:28 am
I think you are right, in that my second post is closer to the actual data.
If we have a range called Tundra and it comes in three different weights and three different price points (and possibly more in the future), then really we have three SUB ranges of Tundra.
Any range that does not have a sub-range, either has lots of products with different prices, or one price for the whole range. This leaves me with this design, which I'm now testing:
RANGE
RangeID (pk)
RangeName
IsPriceByRange <- a flag to help application logic know when to look at the rangeprice table?
PRODUCT
ProductID (pk)
RangeID (fk)
ProductName
ProductColour
ProductPrice <- would be null for those products that don't have individual prices (ie. where IsPriceByRange = true)
SUBRANGE
SubRangeID (pk)
RangeID (fk)
RangeWeightID (fk) <- this will be null for those ranges that have a single price and no sub-ranges
RangePrice
Comments welcome! Open to criticism here! 😉
December 13, 2010 at 5:01 am
Try to think about the real-world system you are modelling. Also think about where most of the queries will be coming from and optimise the database design for this activity.
My guess is that you have product Ids for each variation of range, weight, and colour, so that your customers have a unique Id number they can order when they want a given product. Your design therefore needs to provide a unique reference for each of these variations.
My guess is also that product sales will form the overwhelming majority of the transactions, so your design needs to be optimised to make sales activity fast and easy.
You may be looking at a design where the Product is one of your fact tables, and attributes such as colour, weight and range are dimensions. When the product is being defined, then the correct values of colour, weight and range can be selected. There will be an obvious complication in that not every range will allow for all combinations of colour and weight, so you need appropriate validation to ensure that only the correct values are used.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
December 15, 2010 at 5:28 am
Thanks for the tips.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply