Re: DB structure for logically similar objects in different states... - Mailing list pgsql-general

# eci.souji@gmail.com / 2006-05-28 16:13:20 -0400:
> Basically we've got several different "states" that an item can be in.
> From what I've seen the way many places seem to deal with them is
> something along the lines of making bool values that act as
> switches...
>
> Ex:
> table items:
> item_id
> name
> description
> is_active
> is_sold_out
> is_banned
>
> Now we've started to see some problems with this sort of design.
> Namely we need to run sanity tests on every page that hits the items
> table to make sure is_active is true, is_sold_out is false, is_banned
> is false so on and so forth.  I was thinking of splitting up states
> into different tables ala...
>
> table items_active:
> item_active_id
> name
> description
>
> table items_sold_out:
> item_sold_out_id
> name
> description

    would views help?

    CREATE VIEW items_to_sell AS
     SELECT item_id, name, description
     FROM items
     WHERE is_active = 1 AND is_sold_out = 0 AND is_banned = 0;

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE.             http://bash.org/?255991