pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Table design issue....
5+ messages / 3 participants
[nested] [flat]

* Table design issue....
@ 2001-06-11 14:38  pierre@kahuna.versions.com
  0 siblings, 2 replies; 5+ messages in thread

From: pierre@kahuna.versions.com @ 2001-06-11 14:38 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Table design issue....
@ 2001-06-11 17:25  Alex Pilosov <alex@pilosoft.com>
  parent: pierre@kahuna.versions.com
  1 sibling, 1 reply; 5+ messages in thread

From: Alex Pilosov @ 2001-06-11 17:25 UTC (permalink / raw)
  To: pierre@kahuna.versions.com; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Table design issue....
@ 2001-06-11 17:28  pierre@kahuna.versions.com
  parent: Alex Pilosov <alex@pilosoft.com>
  0 siblings, 0 replies; 5+ messages in thread

From: pierre@kahuna.versions.com @ 2001-06-11 17:28 UTC (permalink / raw)
  To: alex@pilosoft.com; +Cc: pierre@kahuna.versions.com; pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Table design issue....
@ 2001-06-11 17:44  Josh Berkus <josh@agliodbs.com>
  parent: pierre@kahuna.versions.com
  1 sibling, 0 replies; 5+ messages in thread

From: Josh Berkus @ 2001-06-11 17:44 UTC (permalink / raw)
  To: pierre@kahuna.versions.com; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Table design issue....
@ 2001-06-11 19:09  Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Josh Berkus @ 2001-06-11 19:09 UTC (permalink / raw)
  To: pierre@kahuna.versions.com; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2001-06-11 19:09 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-11 14:38 Table design issue.... pierre@kahuna.versions.com
2001-06-11 17:25 ` Alex Pilosov <alex@pilosoft.com>
2001-06-11 17:28   ` pierre@kahuna.versions.com
2001-06-11 17:44 ` Josh Berkus <josh@agliodbs.com>
2001-06-11 19:09 Re: Table design issue.... Josh Berkus <josh@agliodbs.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox