Hi, I was wondering if anyone had any experience with this type of
setup and could share what they've learned.
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
The upside to split up tables is that we don't have to run sanity
checks all over the place and this setup allows us to replicate the
items_active table (the most frequently hit one) out to other DB boxes
to help alleviate some of the select load. One of the downsides to
this setup is we lose the power of a "primary" listing_id. The only
way around that I could think of would be to have a separate listing
table that kept track of what "state" an item was in and pointed to
the primary key of that item in whatever state table it belonged too.
table listings:
listing_id
state (active, sold_out, banned, etc)
state_id
Does anyone have any experience they could share regarding this setup?
Any advice/thoughts would be greatly appreciated.
- Ec