Re: DB structure for logically similar objects in different

# 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

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991