How would you design this table? (1 Viewer)

[jonathanchye]

How would you design this table? (Price list lookup)

I have to design a price list table which is currently done in Excel.

The table looks like this :

[Headers]

Product Group | Layers | (this are quantity bands) 0-100 | 101-500 | 501-1000| etc
Test1 | 2 | £10(normal) £9(special)
The quantity band currently is fixed to 5 bands but would need to be flexible. There are also 2 prices for each of the quantity band (normal/special)

At the moment my table design looks like this:
ID
fkSupplierID
fkProductGroupID
txtLayers (value list)
intMinQty
intMaxQty
curNormalPrice
curSpecialPrice

this works quite well with the query to return price based on product group, layer and order qty. However I am not very sure if this is the best way to design this. I am just thinking about maintenance - for example when the supplier puts in a price change or when the quantity band changes. The current format (quantity band) is based on a major supplier but in the future we would like to adopt this for any supplier.

Can anyone comment please?

 

[CJ_London]

I would add three more fields:

PriceFrom - Date
PriceTo - Date
TimeStamp - Date, default=now()

The first two fields are only required if you need to look backwards and forwards in time. The Timestamp field is a definite requirement.

You use the timestamp field to select the latest input price

You use the PriceFrom fields to determine what price was effective on what day (i.e. select max pricefrom where pricefrom<=DateInQuestion)

You use PriceTo to 'close off' PriceFrom when there is either a gap in pricing or the pricing comes to an end

 

[jonathanchye]

Good shout about the price from and to dates! This would be a good way to tracking which price versions to cost. I feel that ultimately this will be linked to post costing but at the moment I am tasked with converting current spread sheet Vlookup-style pricing to a database.

My only bugbear is with the price range design as this varies with suppliers (some can only have one price) but I guess this is a trade-off from spread sheet to database.

I would add three more fields:

PriceFrom - Date
PriceTo - Date
TimeStamp - Date, default=now()

The first two fields are only required if you need to look backwards and forwards in time. The Timestamp field is a definite requirement.

You use the timestamp field to select the latest input price

You use the PriceFrom fields to determine what price was effective on what day (i.e. select max pricefrom where pricefrom<=DateInQuestion)

You use PriceTo to 'close off' PriceFrom when there is either a gap in pricing or the pricing comes to an end

 

[CJ_London]

My only bugbear is with the price range design as this varies with suppliers (some can only have one price) but I guess this is a trade-off from spread sheet to database.

No - if that is the case redisign your table as follows

ID
fkSupplierID
fkProductGroupID
txtLayers (value list)
intMinQty 
intMaxQty
PriceType - Normal; Special
curPrice
PriceFrom
PriceTo
TimeStamp

 

[jonathanchye]

Curious question - from a design perspective what's the difference between having a marker for normal/special price and one price column as you've suggested vs 2 price columns?

 

[CJ_London]

More flexibility - what if a supplier has more than two price types or the special and normal prices are for differnet periods? With this structure you should be able to map any pricing structure the suppliers throw at you

 

[jonathanchye]

More flexibility - what if a supplier has more than two price types or the special and normal prices are for differnet periods? With this structure you should be able to map any pricing structure the suppliers throw at you

Good point thanks I was told there will ever only be 2 prices for this table but it's best to build for something to allow more