Re: Table design issue....

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
>