Re: Help with complex db design concepts
Date: Thu, 09 May 2002 19:37:46 GMT
Message-ID: <MPG.17449aa38a0348c7989706_at_news.easynews.com>
In article <185bc2bb.0205081416.bd80df4_at_posting.google.com>, zaq42
_at_yahoo.com says...
> I know this is difficult, but I'm trying to find a innovative way to
> glue this enterprise-level applciation together.
Seems to me you've got a subtype-supertype hierarchy here. SpaceEntity is the supertype, all others (like Buildings) are subtypes. You've also got a many-to-many between the SpaceEntity supertype and Doc entity. This would mean some sort of association table between Doc and SpaceEntity.
http://www.utexas.edu/cc/database/datamodeling/dm/hierarchies.html
You've got options as to how to physically implement this logical structure. Personally, I'd implement each subtype as a separate table that has a one-to-one identifying relation with the supertype. The SpaceEntityID would migrate one-to-one into the related subtype table as a foreign key that is also the primary key. (I'd add a trigger or stored procedure, if possible depending on your platform, to maintain that any subtype entry is unique across all subtype tables to make sure that , for example, SpaceEntityID 3 isn't both a Building and a Lake!)
I might or might not add an attribute in the supertype that relates the type of SpaceEntity depending on the output or business rules needed.
Common attributes collect in the supertype, and specialized in the subtypes.
(Any opinions expressed are strictly mine only and not my employer's)
Paul Tiseo, Intermediate Systems Programmer Reply To: 123tiseo.paul_at_123mayo.edu (drop the numbers) Received on Thu May 09 2002 - 21:37:46 CEST