Entity Relationship Diagramming

Question

I'd like to improve my understanding of cardinality constraints in ER diagrams.

I have two entities:

1. User
2. Location

But, I want the relationship between these two entities to be many-to-many (a user can be in many locations and a location can have many users).

To do this I need to introduce an association class UserLocation.

Is it correct to say I now have 3 entities?

If I were to draw an ER diagam of the above, would I draw in the UserLocation entity, and would the cardinality look like this?

User 1 ------ * User Location *  ------ 1 Location

Answer 1

You do not need a third entity.

In the Entity world, this is modeled like this:

• The entity User has a list of Locations.
• The entity Location has a list of Users.

In the Relational world, this is modeled like this:

• A table USER, with primary key USER_ID
• A table LOCATION, with primary key LOCATION_ID
• A table USER_LOCATION, with two foreign keys, one to each of the above tables.

I believe that in the Relational diagram the "intermediary" is not visible. So, I think you would need something like this:

User * ------- * Location

Answer 2

It all depends on the type and level of entitiy mapping you are doing.

You can express the relationship as it is

Entity 1 <> Entity 2

Such as using the 'Crows Feet' to represent the many relationship.

When normalising the map you would break up the many to many relationships with an intermediate table, this would normally contain the primary keys of both of the many tables

Entity 1 > Entity 3 < Entity 2

Where Entity 3 (your intermedite table) would hold PK's for both tables, and ocassionally any other data, such as a unique ID etc.