Re: [GENERAL] DB structure for logically similar objects in different
# [EMAIL PROTECTED] / 2006-05-29 08:10:43 -0400: Roman Neuhauser wrote: # [EMAIL PROTECTED] / 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] DB structure for logically similar objects in different
Roman Neuhauser wrote: # [EMAIL PROTECTED] / 2006-05-29 08:10:43 -0400: Roman Neuhauser wrote: # [EMAIL PROTECTED] / 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? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] DB structure for logically similar objects in different
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] DB structure for logically similar objects in different
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/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] DB structure for logically similar objects in different states...
# [EMAIL PROTECTED] / 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] DB structure for logically similar objects in different
Roman Neuhauser wrote: # [EMAIL PROTECTED] / 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] DB structure for logically similar objects in different
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? ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
