Roman Neuhauser wrote:
# eci.souji@xxxxxxxxx / 2006-05-29 08:10:43 -0400:
Roman Neuhauser wrote:
# eci.souji@xxxxxxxxx / 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?
