DB structure for logically similar objects in different states...

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/

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-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

# 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