Re: DB structure for logically similar objects in different

Roman Neuhauser wrote:
> # eci(dot)souji(at)gmail(dot)com / 2006-05-29 08:10:43 -0400:
>
>>Roman Neuhauser wrote:
>>
>>># eci(dot)souji(at)gmail(dot)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?