I am planni开发者_运维知识库ng to create a pricing matrix for a project in Rails. It's supposed to be a table of destinations and departure locations, and prices depending on where you came and are planning to go.
I am kinda undecided on how to better do this: either making a table in the db for this matrix, or making a mega array of constants. Problem is, the client should be able to edit this matrix so its most likely going the database route.
Anyhow, what's a good schema for this? Destination id, Departure id, then price? destination and departure ids will be foreign keys for a table containing all possible locations. Is there a better way of doing this?
Make it a db table.
The only thing constant about prices is that they change.
Added:
Pricing (also called product factoring) is something that I have a lot of experience with.
Your clients may also ask/or appreciate added pricing tools to help them get things right. Eg, your sw:
- could have reports that show the the prices in a manner that is designed for the people doing the pricing.
- report on the highest, lowest prices (to help catch data entry errors)
- Check out visual design of quantitative information for ideas on how to visually show the prices for the destination pairs
- make sure that a destination/departure pair is only added exactly once. (No duplicates in the other direction.)
- etc
You may also need to worry about effective dates for the pricing. Ie how to roll-out a new set of prices in a co-ordinated way.
I would use two tables, Location and TravelPrice.
Location
----------
LocationID --PK
Name
TravelPrice
-------------
TravelPriceID --PK
DepartureLocationID --FK to Location
DestinationLocationID --FK to Location
Price
StartDate --date the price is effective from
EndDate --date the price is effective to (or NULL)
This allows you to keep a price history, important for reporting, billing, etc. Ideally you would have trigger on the TravelPrice
table ensuring that there are no gaps or overlaps in dates for a given DepartureLocationID
/DestinationLocationID
combination, and that there is only one record with a NULL EndDate
for that pair.
I would add this to a 3-column table because it's not necessarily a matrix - you might not travel from all places to all other places. You want to be able to edit it. As soon as you are done with your hard-coded version, you'll be asked to edit it.
LeavingFrom, TravellingTo, Price
Also, as the list of destinations grows, query performance and code maintenance will become a factor.
精彩评论