Обсуждение: Inheritance design question

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

Jean-Christian,

> 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?

That is a sound application of the table inheritance concept.

> 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.

It has several subtleties, the most tricky of which are the handling of keys
and indexes, which do not necessarily span the inherited tables.

Myself, I don't use table inheritance because I am a relational database
fanatic, but instead use FK related child tables.  (Lots of people don't
agree with me though, or we wouldn't have table inheritance)

 For example, an alternative approach would be to:

1) add one column to your main table called "sales_status".  This would then
key to a lookup table with 1 = retail sales and 2 = bargain bin.  Such a
field could later become useful by adding additional exclusive statuses, such
as -1 = not yet available and 99 = permanently out of stock.   If you already
have a "status" column, why not expand it to cover the "bargain bin" flag
instead of adding a new column.

2) Add a child-FK table called bargain_bin, with 3 columns:
    id INT NOT NULL PRIMARY KEY REFERENCES main_table (id) ON DELETE CASCADE,
    bargain_bin_price NUMERIC NOT NULL,
    stock_quantity INT NOT NULL

This allows you to track the bargain bin items without either:
a) cluttering up the main table with new columns which will be NULL for 90% of
entries;
b) having to move records btw. the main and a child-inherited table.

--
Josh Berkus
Aglio Database Solutions
San Francisco