Thread: DB structure for logically similar objects in different states...
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
Eci Souji wrote: > 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. You could just have a "listing_id" sequence that you get new numbers from and use that in your other tables. It can still be a primary key because it will be unique across your different tables. -- Postgresql & php tutorials http://www.designmagick.com/
# eci.souji@gmail.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;
--
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
Roman Neuhauser wrote: > # eci.souji@gmail.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
Chris wrote: > Eci Souji wrote: > >> 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. > > > You could just have a "listing_id" sequence that you get new numbers > from and use that in your other tables. It can still be a primary key > because it will be unique across your different tables. > A shared sequence would help, but if I tried to use the single listing_id as my reference how could I figure out what table (and thus what state) the item was in?
# eci.souji@gmail.com / 2006-05-29 08:10:43 -0400:
> Roman Neuhauser wrote:
> ># eci.souji@gmail.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
Roman Neuhauser wrote: > # eci.souji@gmail.com / 2006-05-29 08:10:43 -0400: > >>Roman Neuhauser wrote: >> >>># eci.souji@gmail.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 > 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?
On May 30, 2006, at 5:48 AM, Eci Souji wrote: > 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? That depends entirely on your access patterns and how your data is broken down. Moving data between tables will be more involved from a code standpoint, and thus more prone to errors. On other databases it would also be less efficient, but because of how PostgreSQL does MVCC I don't think it would make too much of a difference performance-wise. You also need to consider the breakdown of your data. If you've got one set of conditions that are very prevalent, you can see some storage (and hence, speed) gains by splitting into different tables, perhaps by having one table for the common case and another one that handles all the uncommon cases. For example, if you have a users table, if you have a very large number of users it will probably help to have a seperate user_lockout table that contains only the user_id of users that are denied access to the system. The downside is that you have to do a join every time you want to check that. The upside is that you're saving as much as 4 bytes in the user table, which depending on how many users you have and your access patterns can add up. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461