Re: Table Design Advice - normalizing

Make an Entities table with a Type column (int) and all common attributes (LongDesc, ShortDesc, ActID, etc).

Create an EntityType table :
create table EntityType (
TypeID int,
TypeDescript varchar(50))

This will have 4 records, one for each Entity type

For each Entity in you Entities table, use the TypeID in EntityType as a foreign key to the Type column in the Entities Table.

For each EntityType that has "extra" fields, create a new table that has a key pointing to the Entities table and the unique attributes.

You will end up with more tables this way, but your data is more concise. You also avoid the possibility of a SimpleFP also getting added to the ComplexFP table on accident.

Without knowing what you're actually trying to track, there's a chance that a different schema would be better. If you have any problems, questions, or just want to bounce some more ideas around, let me know.

Todd

"April" <PrivateBenjamin_at_hushmail.com> wrote in message news:54df0379.0110221144.24e2ff74_at_posting.google.com...
> Hi,
>
> I have potentially 4 entities which are all of the same "master
> entity" type (if you can call it that). Each entity shares the same
> subset of 6 fields, but 3 of the entities have additional fields.
>
> ex. Entity SimpleFP : fields - LongDesc,ShortDesc,ActID,Qty,Year,Month
> Entity ComplexFP: fields -
> LongDesc,ShortDesc,ActID,Qty,Year,Month,UnitPrice, ISDate.
>
> The proper thing to do, I believe, is to create 4 tables, one for each
> entity. However since they share so much in common, I was wondering if
> there was an efficient way that I could create 1 table for all
> entities? It seems like a waste to have four tables.
>
> Could anyone provide suggestions on this issue?
>
> Thank you,
> April
Received on Thu Oct 25 2001 - 22:07:42 CEST