Database - Dynamic table selection

I have a strange problem and cant seem to find an answer anywhere. I am building a small Ruby on Rails application to calculate electricity costs.

For this I need to know what Tariff a person might be on, which is fine for normal tariffs. (e.g. Usually people are on a set cost per kilowatt for day and night => Tariff Table with static data for each type of tariff)

But my problem is that some people are now on what is called a dynamic tariff. So basically every hour the cost goes up or down depending on the overall demand on the electricity network. So now I have both static tariff data and dynamic tariff data.

I was thinking of something like this:

Customer Table => ID, Name, TariffID, etc etc

Tariff Table => ID, Type

Static Tariff Table => ID, Cost per KiloWatt, TariffID, etc

Dynamic Tariff Table => ID, Cost per KiloWatt, TimeStamp, TariffID

And then depending on the type of tariff in tariff table I would know to use the static or dynamic table.

Also, I need to record the timestamp of each dynamic tariff to calculate the cost for each hour of the day against electricity use of each hour of the day, but I dont need it for the static one ;)

To me this looks a bit heavy and I was wondering if anyone can suggest a more subtle solution?

Thanks in Advance,

Seamu开发者_如何学Gos

---

You may want to read up on Single Table Inheritance - this seems like a good use case for it.

There is a lot of good info on it in this Stack Overflow question as well.

It seems like you want a tariffs table, a Tariff class with all your common code, and then a StaticTariff and DynamicTariff classes that inherit from Tariff. This way, you can override methods in the subclasses to calculate cost per hour. The StaticTariff will be easy since it likely returns a column value in your db, while DynamicTariff probably needs to do a calculation based on dates/times.

Any shared code between the two can be safely placed in your parent Tariff class and you should be on your way.

---

Can your users switch tariffs over time, for example from a static to a dynamic one? If so your schema won't be sufficient.

Also, you don't say if there's more than one time-based dynamic tariff.

Using STI could help you and fits in the sense that StaticTariff and DynamicTariff both pass the "is a " Tariff test. However, those two concrete implementations are sufficiently different that it probably doesn't make sense to store their details in the same table.

I'd suggest something like a common api for both along the lines of TariffCalculator.cost(:user => user, :time_range => time_range) and deal with selecting the concrete implementation internally. Don't get bogged down in the database structure at this point, just focus on keeping the interface clean and well tested. That way you can refactor with impunity later :D