Re: Table design decision

From: Jim Smith <jim_at_jimsmith.demon.co.uk>
Date: Fri, 13 Jan 2006 14:28:27 +0000
Message-ID: <MVWG3xgLk7xDFwON@jimsmith.demon.co.uk>

In message <1137143732.207358.169350_at_g44g2000cwa.googlegroups.com>, Tarby777 <nick_williamson_at_mentorg.com> writes
>Well, I don't think the 50 existing tables are badly designed; there
>are 50 different kinds of objects that can be created in the app, and
>each one is sufficiently different from the others to merit having its
>own table so I don't think that that part of the schema needs to
>change. The bad design I was alluding to is that the user-defined
>properties for all these different kinds of objects are all stored in
>one table, which has 50 FKs. I imagine that when the app started out,
>there would have been substantially less than 50 object types, and it's
>probably just evolved into the current monster over time.
>
>Anyway, after much typing, I now have the DDL for my 50-new-tables
>option. If anything earth-shattering comes out of my testing, I'll
>report back. Thanks again to all who posted.
>
>Regards,
>Tarby
>

What about
table object_common
 objid primary key
 <other properties common to all object types>

table x
 x_id primary key

 .
 .
 .

 x_object foreign key references object_common

table object_a
  objid primary key and foreign key references object_common.   <others>
table object_b
  objid primary key and foreign key references object_common.   <others>

repeat for ever

-- 
Jim Smith
I'm afraid you've mistaken me for someone who gives a damn.

Received on Fri Jan 13 2006 - 08:28:27 CST