create a pricing matrix - sql or array of constants?

Question

I am planning 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?

Answer 1

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.

Answer 2

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.

Answer 3

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.