CODEWITHSUNDEEP
sqldatabaseoracle-sqldeveloperrelationshipentity-relationship
RealGonPC
RealGonPC

Reputation: 11

Entity has relationship with a second entity and with bridge entity between both entities

I am pretty new to Database design and I wish to know if the following design is accepted.

As you can see in my ER Diagram, the Museum entity has a relationship of one-to-many with the Guard, as many guards can be hired for a museum and a one-to-many relationship with the Cleaner. The bridge entity Guarding between Museum and Guard has a relationship of many-to-one with Museum and many-to-one with Guard. Am I allowed to have both a relationship between the Guard and Museum and a bridge entity between them?

enter image description here

Upvotes: 0

Views: 148

Answers (2)

The Impaler
The Impaler

Reputation: 48865

There can be many -- and I mean many -- relationships between each pair of entities. There's no limitation at all, where each relationship has its own specific meaning. This also includes reflexive relationships -- the ones that happen between an entity and itself.

Now, that can be also combined with indirect relationships between each pair of entities, something that is also not limited.

Therefore, you can have unlimited number of direct and indirect relationships between each pair of tables as well. Just make sure you understand the semantics of each entity and of each relationship.

Upvotes: 0

Randy
Randy

Reputation: 16673

Yes you are allowed.

But then ask yourself why?

Why do I need a specific FK to museum on the guard... Is the Guard somehow defined by that museum? In this case, I think you will find that, no, the guard is a separate thing than the museum, and does not depend upon a museum for his existence. So to properly normalize, you would not include museum on the guard.

Then look at the Guarding table. What did I intend when placing museum_id directly on the guard, was it to indicate some kind of home base, or hiring museum, or most frequently worked at museum? all of these can be captured with a new column on Guarding like relationship_type where you can specify the relationship between the guard and the museum.

finally, some guard may switch to new museums etc, so you should have some dates when the guarding relationship is valid - like a start_date and End_date to capture when this guard worked at this museum.

finally, why not go through the same thought process for cleaners? you'll find that they also should have a linking class.

then still more - why aren't these two related things just people or employees with a column that indicates their job title?

anyhow - you're on the right track..

Upvotes: 0

Related Questions