I'm just about to start reading up on table inheritance in posgres but
before I do I wanted some advice as to whether or not inheritance is a
good way to solve my problem or not.
I have a database for a company that sells videos. Some of those videos
eventually become "out of print". Currently out-of-print videos get
marked as such in the database. So far so good ...
However if there is any stock left-over for an out-of-print video it
gets put into a bargain bin for cheap sale.
The company would like to upgrade their existing database design to keep
track of these bargain bin sales ...
The current company's database contain information on the videos such as
the retail price and whether the video is out of print or not.
The problem is:
- the company does not keep stock information in the database
- not all out-of-print videos that have left-over stock become bargain
bin items
- someone at the video "warehouse" actually decides which out-of-print
videos to put in the bargain bin, how many *and* at what price
- not all bargain bin videos have the same bargain bin price
A quick hack would be to add three new fields to the current product table:
- is_bin_item boolean
- bargain_bin_price
- bargain_bin_quantity
But that's a hack. I want something better :)
I was thinking that one way to solve the problem would be to create a
"bargain bin item" table that inherits from the current product
information table. The child table would add the following extra fields:
- bargain bin price
- stock quantity
Does that sound like a sound design?
I've never used inheritance in postgres so I am worried that even though
it looks nice it might have some subtleties I won't find out about
until it's too late.
Any advice is greatly appreciated!
--
Jean-Christian Imbeault
