Thread: Database with "override" tables
Hello, I am in a situation where I have various tables (including data such as a product list) that are read-only to me. I wish to provide the functionality of changing this table: Removing items, modifying items, creating new ones. My original idea is to use a second table that is formatted similarly to the first, read-only table. However, I can't just duplicate all the data and work in a separate table, as the original table may be modified by an outside source at any time. Generally, I prefer to use the updated data from the read-only table, unless there has been reason to modify it in the past -- in which case the update may be safely ignored, and I would continue to use the second table. The most effective set up I have come up with thus far is as follows: CREATE TABLE initial_table (initial_id SERIAL PRIMARY KEY, desc TEXT); CREATE TABLE override_table (override_id SERIAL PRIMARY KEY, initial_id INT, desc TEXT); An entry in override_table with an initial_id matching an initial_id in initial_table would take precedence over the entry in initial_table. A fairly simple SELECT statement can return the data I require: SELECT COALESCE(ot.desc, it.desc) FROM initial_table it FULL JOIN override_table ot ON it.initial_id = ot.initial_id; So far so good, but then when I need to reference data in those two tables from somewhere else, I need to reference both initial_id and override_id on the result set from that table. I also end up using COALESCE an uncomfortable amount (though, if this is required I will do so). I would prefer to treat the initial + override tables as a single SELECT set, but cannot come up with a way to do this cleanly, especially with regards to the IDs. I am willing to abandon this format of "overriding" the initial table if it is way off-course, provided that the initial data be considered read-only and update-able in the absence of "override" data. All suggestions are greatly appreciated! Thanks in advance, Mike.
I think I have a similar situation involving the naming of assets, where the usual asset description is used, but users can enter a description in a separate table which 'overrides' the original name with a name that is more familiar to the individual. IF THIS IS WHAT YOU WANT, it was accomplished by doing a UNION between two select statements, like this: select <override values> from foo1 union select <normal values> from foo2 where <record notin foo1>; Hope this helps. -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Michael Burke Sent: Monday, December 05, 2005 2:07 PM To: PGSQL-SQL Subject: [SQL] Database with "override" tables Hello, I am in a situation where I have various tables (including data such as a product list) that are read-only to me. I wish to provide the functionality of changing this table: Removing items, modifying items, creating new ones. My original idea is to use a second table that is formatted similarly to the first, read-only table. However, I can't just duplicate all the data and work in a separate table, as the original table may be modified by an outside source at any time. Generally, I prefer to use the updated data from the read-only table, unless there has been reason to modify it in the past -- in which case the update may be safely ignored, and I would continue to use the second table. The most effective set up I have come up with thus far is as follows: CREATE TABLE initial_table (initial_id SERIAL PRIMARY KEY, desc TEXT); CREATE TABLE override_table (override_id SERIAL PRIMARY KEY, initial_id INT, desc TEXT); An entry in override_table with an initial_id matching an initial_id in initial_table would take precedence over the entry in initial_table. A fairly simple SELECT statement can return the data I require: SELECT COALESCE(ot.desc, it.desc) FROM initial_table it FULL JOIN override_table ot ON it.initial_id = ot.initial_id; So far so good, but then when I need to reference data in those two tables from somewhere else, I need to reference both initial_id and override_id on the result set from that table. I also end up using COALESCE an uncomfortable amount (though, if this is required I will do so). I would prefer to treat the initial + override tables as a single SELECT set, but cannot come up with a way to do this cleanly, especially with regards to the IDs. I am willing to abandon this format of "overriding" the initial table if it is way off-course, provided that the initial data be considered read-only and update-able in the absence of "override" data. All suggestions are greatly appreciated! Thanks in advance, Mike. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
Lane Van Ingen wrote: >I think I have a similar situation involving the naming of assets, where >the usual asset description is used, but users can enter a description in >a separate table which 'overrides' the original name with a name that is >more familiar to the individual. > >IF THIS IS WHAT YOU WANT, it was accomplished by doing a UNION between two >select statements, like this: > select <override values> from foo1 > union > select <normal values> from foo2 > where <record not in foo1>; > >Hope this helps. > > > That almost works, and it is a much cleaner query than I had before. However, there's a possibility that some columns in the overridden table are NULL (indicating that the original value should be used). So, a particular asset may contain a description and price; the price may be NULL, meaning the read-only value should be used, but the user may have attached a special description as we previously outlined. What I'm looking for is the ability to, perhaps, "overlay" foo2 onto foo1, joined on foo1_id. Then, NULL values in foo2 become "transparent" and we see the foo1 values behind them. Presently I am using COALESCE() for every pair individually, ie. COALESCE(foo2.price, foo1.price), COALESCE(foo2.descr, foo1.descr), ... and then doing a FULL JOIN. This works. I'm starting to wonder if it's worth the extra hassle, I may just use your suggested UNION method instead. Thanks again, Mike.
Not quite sure how to answer this, but one thought does occur to me: I was perhaps assuming that an override table would override an entire record in the 'original' table(that is what we are doing), and we require that critical fields in the override field be NOT NULL (and in some cases, provide DEFAULT values). Will that help? -----Original Message----- From: Michael Burke [mailto:michael@engtech.ca] Sent: Tuesday, December 06, 2005 9:55 AM To: Lane Van Ingen Cc: PGSQL-SQL Subject: Re: [SQL] Database with "override" tables Lane Van Ingen wrote: >I think I have a similar situation involving the naming of assets, where >the usual asset description is used, but users can enter a description in >a separate table which 'overrides' the original name with a name that is >more familiar to the individual. > >IF THIS IS WHAT YOU WANT, it was accomplished by doing a UNION between two >select statements, like this: > select <override values> from foo1 > union > select <normal values> from foo2 > where <record not in foo1>; > >Hope this helps. > > > That almost works, and it is a much cleaner query than I had before. However, there's a possibility that some columns in the overridden table are NULL (indicating that the original value should be used). So, a particular asset may contain a description and price; the price may be NULL, meaning the read-only value should be used, but the user may have attached a special description as we previously outlined. What I'm looking for is the ability to, perhaps, "overlay" foo2 onto foo1, joined on foo1_id. Then, NULL values in foo2 become "transparent" and we see the foo1 values behind them. Presently I am using COALESCE() for every pair individually, ie. COALESCE(foo2.price, foo1.price), COALESCE(foo2.descr, foo1.descr), ... and then doing a FULL JOIN. This works. I'm starting to wonder if it's worth the extra hassle, I may just use your suggested UNION method instead. Thanks again, Mike.
Lane Van Ingen wrote: >Not quite sure how to answer this, but one thought does occur to me: I was >perhaps assuming that an override table would override an entire record in >the 'original' table(that is what we are doing), and we require that >critical fields in the override field be NOT NULL (and in some cases, >provide DEFAULT values). Will that help? > > I fought with the UNION method for a while before backing off to my extremely-long-query system with a FULL JOIN: SELECT COALESCE(ot.desc, it.desc) FROM initial_table it FULL JOIN override_table ot ON it.initial_id = ot.initial_id; It's not so bad until you get to cross-reference other tables that have override tables, but then it just becomes pretty ugly. So I'll stick with that for now. If the override table did indeed override entire records in the original table, the UNION would have worked beautifully and probably made things cleaner. Thanks for the advice. Mike.