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
