Price / rate card table structure

[JBRTaylor]

Hi,
I was hoping someone here may be able to offer me some advice on how best to approach a rate card i am building. At the moment i have one table which lists all of the equipment that is available for hire. In a second table "tblRateCard" i use the key from the equipment table, the tariff and then another field for the price.

If one item of kit has two prices it will appear twice in the rate card but with different tariff names and prices.

I then intend to use a query to filter down to the correct price based on the equipment key and the tariff.

Is this an acceptable way to set this out or am i setting myself up for a world of pain in the future?

Thanks in advance
Jon

 

[plog]

You might be. Are EquipmentID, Price and Tariff the only fields you have in tblRateCard? If so, the answer is yes because you will need an additional field to determine which price to use. So, how do you determine which record to use from tblRateCard when you have multiple records for the same EquipmentID?

 

[JBRTaylor]

Hi, Thanks for the speedy reply. Yes at the moment it is just the price that is in the Tariff table which looks somthing like this:
Equipment key Tariff Price
1 TariffA 10
2 TariffA 5
1 TariffB 6

So to find the correct price i will use a query to filter the equipment key (Equipment key 1), and then the Tariff (tariff B) to give the result: 6.

Jon

 

[plog]

Oh, ok, I thought Tariff was a numeric value added to the Price. It sounds like you have all that you need in that table and its structured properly.