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.
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
Additional identifying Information
Another table, having a 1:M relationship with HOLES
Additional Depth_Interval Info
Another table, having 1:M relationship with HOLES
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.