Re: DB structure for logically similar objects in different

Chris wrote:
> 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.
>

A shared sequence would help, but if I tried to use the single
listing_id as my reference how could I figure out what table (and thus
what state) the item was in?