Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2006

    Help with ER modeling - same relationship with multiple entities?

    Hi, I'm new to the forums, I mean. I'm also new to database design.

    I'm trying to make an ER diagram up at work here, and I'm stuck. What do you do when an entity type participates in the same relationship with more than one other entity type?

    For example, my company makes drill holes. Each hole has a collar (the part of the hole at 0 metres deep), and also depth intervals (e.g. the space between 52 and 54 metres deep, usually about 2 m) and ore intervals (e.g. the space between 38 and 62 metres deep where there is a significant amount of gold). So I have four entities - hole, collar, depth interval, ore interval. I can't make them class/subclass relationships though, since none of them is a TYPE of hole - just a part of one. A hole contains one collar, one or more depth intervals, and zero or more ore intervals. A point at any given depth in the hole will be included in one depth interval, and in zero or more ore intervals (though the depth point isn't an entity - that bit was just for clarification). What's the best way to show all this?

    Or here's a separate example. A sample gets taken from every depth interval. However that depth interval could be in a drill hole, or it could be in a trench, and those are both separate entities.

    Any ideas? I am really stuck - I'm not really a database person but since it's a small company I have to learn how to do it all myself.

    Thanks so much

  2. #2
    Join Date
    Feb 2005
    Colorado Springs
    I noticed you haven't received any replies, so I'll give it a shot. The first thing in an ER diagram is defining the entity. Here, perhaps "hole" would be an entity. Each hole has one collar, possibly multiple depth intervals, and possibly multiple ore intervals. So, I'd create a table

    Hole_ID (PK)
    Additional identifying Information

    Another table, having a 1:M relationship with HOLES
    Depth_Interval_ID (PK)
    Hole_ID (FK)
    Additional Depth_Interval Info

    Another table, having 1:M relationship with HOLES
    Ore_Interval_ID (PK)
    Hole_ID (FK)
    Additional Ore-Interval Info

    Hope that gets things started - it may be that Ore_Interval should actually reference DEPTH_INTERVAL rather than HOLES - I couldn't tell from your description.

  3. #3
    Join Date
    Mar 2006
    Thanks I'll give that a try. I'm also thinking of merging the collar with the hole itself like you've suggested and then including depth and ore intervals as subclasses of "interval."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts