Обсуждение: Table design issue....
Hi all, I've got a situation where I need to be able to query for the same sort of data across multiple tables. Let me give some example tables then explain. create table t1 ( t_attr1 text[], t_attr2 text[] ); create table a1 ( a_attr1 text[], a_attr2 text[] ); create table c1 ( c_attr1 text[], c_attr2 text[], c_attr3 text[] ); In each of the above tables *_attr*[1] contains a flag that determines what type of attribute it is. t1.t_attr1[1] == a1.a_attr2[1] == c1_.c_attr3[1] == FLAG In otherwords, the attribute with the specific flag in quesiton is not known at runtime, unless I keep a table with the column names and table names setup. Also, new *1 tables could be created dynamically with new attr*'s, and the number of columns within the tables isn't going to be the same. What I need to be able to do is say something like: "For ALL *1 tables with *_attr*[1] == FLAG return rows with VALUE" Ideas? Comments? Suggestions? Am I being crazy? Pierre
Yes, this seems like a bad design. If you feel you need to join potentially unlimited number of tables, that means all of those tables really belong in one table. If you can explain why do you think you need separate tables...? On 11 Jun 2001 pierre@kahuna.versions.com wrote: > Hi all, > > I've got a situation where I need to be able to query for the > same sort of data across multiple tables. Let me give some example > tables then explain. > > create table t1 ( > t_attr1 text[], > t_attr2 text[] > ); > create table a1 ( > a_attr1 text[], > a_attr2 text[] > ); > create table c1 ( > c_attr1 text[], > c_attr2 text[], > c_attr3 text[] > ); > > In each of the above tables *_attr*[1] contains a flag that determines > what type of attribute it is. > t1.t_attr1[1] == a1.a_attr2[1] == c1_.c_attr3[1] == FLAG > In otherwords, the attribute with the specific flag in quesiton is not > known at runtime, unless I keep a table with the column names and table > names setup. Also, new *1 tables could be created dynamically with new attr*'s, > and the number of columns within the tables isn't going to be the same. > > What I need to be able to do is say something like: > "For ALL *1 tables with *_attr*[1] == FLAG return rows with > VALUE" > > Ideas? Comments? Suggestions? Am I being crazy? > > Pierre > >
Pierre, > Ideas? Comments? Suggestions? Am I being crazy? Yes. To be blunt, you've picked one of the worst possible database designs for any useful purpose. This is, however, a common mistake as far too many books and training courses teach how to write SQL without teaching how to design a database. What you really want is something like this: CREATE TABLE tables (tableid CHAR(1) NOT NULL PRIMARY KEY); CREATE TABLE attributes (tableid CHAR(1) NOT NULL REFERENCES tables(tableid),attributeid CHAR(1) NOT NULL,CONSTRAINT tab_attr_PKPRIMARY KEY (tableid, attributeid) ); This makes your select statement possible:SELECT tableid FROM attributes WHERE attributeid = 'C'GROUP BY tableid ORDER BYtableid; If your application requirements are more complicated than this, you need to either: a) hire a relational design expert, or b) become one. Books I'd recommend for the latter are Database Design for Mere Mortals and Practical Issues in Database Design (F. Pascal). -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Unfortunately I cannot give out the full reasons behind attempting a design such as this, however let me try and give a generalization. What I need is a way to store different sets of data that are related by a key in different tables. Within each table's set of columns there will exist one attribute who is of a special type. The key is of special interest because it helps define a grouping of rows from within each of the tables as well as across the tables. Now, this special attribute's value must be unique within the grouping that contains the row which has the attribute. In answer to your question about seperate tables, I believe it will be easier to maintain these data sets if they are within different tables, both for insert as well as updates. Also I'm a bit concerned about tuple size limitations, I could very well see 400+ columns if I put everything into a single table. Does this help? Pierre > > Yes, this seems like a bad design. If you feel you need to join > potentially unlimited number of tables, that means all of those tables > really belong in one table. If you can explain why do you think you need > separate tables...? > > > > On 11 Jun 2001 pierre@kahuna.versions.com wrote: > > > Hi all, > > > > I've got a situation where I need to be able to query for the > > same sort of data across multiple tables. Let me give some example > > tables then explain. > > > > create table t1 ( > > t_attr1 text[], > > t_attr2 text[] > > ); > > create table a1 ( > > a_attr1 text[], > > a_attr2 text[] > > ); > > create table c1 ( > > c_attr1 text[], > > c_attr2 text[], > > c_attr3 text[] > > ); > > > > In each of the above tables *_attr*[1] contains a flag that determines > > what type of attribute it is. > > t1.t_attr1[1] == a1.a_attr2[1] == c1_.c_attr3[1] == FLAG > > In otherwords, the attribute with the specific flag in quesiton is not > > known at runtime, unless I keep a table with the column names and table > > names setup. Also, new *1 tables could be created dynamically with new attr*'s, > > and the number of columns within the tables isn't going to be the same. > > > > What I need to be able to do is say something like: > > "For ALL *1 tables with *_attr*[1] == FLAG return rows with > > VALUE" > > > > Ideas? Comments? Suggestions? Am I being crazy? > > > > Pierre > > > > > >
Wow, that is blunt. :-) In all honesty, I can't imagine ever having to join all of that tables at once. I had considered creating an attribute table previously, but was concerned about performance when working to retrieve large numbers of records. Perhaps, the best idea is the one I tried to not use, and that is a seperate table that contains a copy of all of the attribute's that have the specified type. sort of a lookaside table. I was just concenred with referential integrity. Pierre > > Pierre, > > > Ideas? Comments? Suggestions? Am I being crazy? > > Yes. To be blunt, you've picked one of the worst possible database > designs for any useful purpose. This is, however, a common mistake as > far too many books and training courses teach how to write SQL without > teaching how to design a database. > > What you really want is something like this: > > CREATE TABLE tables ( > tableid CHAR(1) NOT NULL PRIMARY KEY > ); > > CREATE TABLE attributes ( > tableid CHAR(1) NOT NULL REFERENCES tables(tableid), > attributeid CHAR(1) NOT NULL, > CONSTRAINT tab_attr_PK PRIMARY KEY (tableid, attributeid) > ); > > This makes your select statement possible: > SELECT tableid FROM attributes WHERE attributeid = 'C' > GROUP BY tableid ORDER BY tableid; > > If your application requirements are more complicated than this, you > need to either: a) hire a relational design expert, or b) become one. > Books I'd recommend for the latter are Database Design for Mere Mortals > and Practical Issues in Database Design (F. Pascal). > > -Josh Berkus > > > > > > ______AGLIO DATABASE SOLUTIONS___________________________ > Josh Berkus > Complete information technology josh@agliodbs.com > and data management solutions (415) 565-7293 > for law firms, small businesses fax 621-2533 > and non-profit organizations. San Francisco >
Pierre, > In all honesty, I can't imagine ever having to join all of that > tables at once. > I had considered creating an attribute table previously, but was > concerned > about performance when working to retrieve large numbers of records. A lot of optimization will be necessessary if you are talking over a million records (or over 100,000 on a slow server). But spreading out the data over several tables will force you to make a *lot* of LEFT JOINs, which sure doesn't help your performance either. > Perhaps, the best idea is the one I tried to not use, and that is a > seperate > table that contains a copy of all of the attribute's that have the > specified type. sort of a lookaside table. I was just concenred with > referential integrity. Ah. I see what you're getting at. Why not use a view, rather than a lookup table? I think that this might overcome your performance issues: CREATE VIEW vw_table_attributes_C AS SELECT tableid, attribute_type FROM attributes WHERE attribute_type = 'C' GROUP BY tableid, attribute_type; SELECT tableid, table_data FROM tables JOIN vw_table__attributes_C USING tableid; This prevents the duplication of data tables, plus gives you the optimization inherent in a view. With regular VACUUM ANALYZE, this should solve your perfromance problems. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco