Re: DB structure for logically similar objects in different

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?

- Ec