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