Re: DB structure for logically similar objects in different

Eci Souji wrote:
> 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.

You could just have a "listing_id" sequence that you get new numbers
from and use that in your other tables. It can still be a primary key
because it will be unique across your different tables.

--
Postgresql & php tutorials
http://www.designmagick.com/