DB structure for logically similar objects in different states...

From: "Eci Souji" <eci(dot)souji(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: DB structure for logically similar objects in different states...
Date: 2006-05-28 20:13:20
Message-ID: fb488260605281313t5d29f65dpac7bda6ca5bbbdf9@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi, I was wondering if anyone had any experience with this type of
setup and could share what they've learned.

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

The upside to split up tables is that we don't have to run sanity
checks all over the place and this setup allows us to replicate the
items_active table (the most frequently hit one) out to other DB boxes
to help alleviate some of the select load. One of the downsides to
this setup is we lose the power of a "primary" listing_id. The only
way around that I could think of would be to have a separate listing
table that kept track of what "state" an item was in and pointed to
the primary key of that item in whatever state table it belonged too.

table listings:
listing_id
state (active, sold_out, banned, etc)
state_id

Does anyone have any experience they could share regarding this setup?
Any advice/thoughts would be greatly appreciated.

- Ec

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2006-05-28 21:36:17 Re: db crash after power crash
Previous Message Jürgen Rose 2006-05-28 20:11:04 Re: pg_dump on 7.4