Re: DB structure for logically similar objects in different

Roman Neuhauser wrote:
> # eci.souji@gmail.com / 2006-05-29 08:10:43 -0400:
>
>>Roman Neuhauser wrote:
>>
>>># 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;
>>
>>Views work for querying the chunks of data that match different states,
>>but if I was looking for information based on a single item_id wouldn't
>>I still need the sanity checks?
>
>
>     No.
>
>     SELECT *
>     FROM items_to_sell
>     WHERE item_id = 123
>
>     will be transformed into something like
>
>     SELECT item_id, name, description
>     FROM items
>     WHERE item_id = 123
>     AND is_active = 1
>     AND is_sold_out = 0
>     AND is_banned = 0
>

Hmmm that works too.  So I guess my next question is which is a better
designed system; one large table with bools and views or six small
tables with stored procs to move data between tables?