| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> comp.databases.theory -> Re: Help with complex db design concepts
In article <185bc2bb.0205081416.bd80df4_at_posting.google.com>, zaq42
@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)
 |
 |