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

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

HOLES
Hole_ID (PK)
Collar
Additional identifying Information

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

Another table, having 1:M relationship with HOLES
ORE_INTERVAL
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.

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."