Re: Table design issue.... - Mailing list pgsql-sql

From Josh Berkus
Subject Re: Table design issue....
Date
Msg-id web-70284@davinci.ethosmedia.com
Whole thread Raw
In response to Table design issue....  (pierre@kahuna.versions.com)
Responses Re: Table design issue....
List 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_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
 


pgsql-sql by date:

Previous
From: "Josh Berkus"
Date:
Subject: Re: problem with Pl/Pgsql function
Next
From: Alex Pilosov
Date:
Subject: Re: finding a maximum or minimum sum