KateMak KateMak - 11 months ago 61
SQL Question

In an ER Diagram, how do I know when you use one or many?

I am trying to understand cardinality in ER diagrams, starting with a simple example of a visitor visiting a city. Thinking about it, you can have many visitors visiting many cities, so I drew the ER diagram as shown. Visitor is an Entity with attributes such as name, Visits is the relationship with the VisitorID and CityID as the primary key and other attributes such as date, and City is an entity, with attributes such as country. This would mean 0 to many visitors can visit 0 to many cities. Does this mean that in a database with this ER diagram, I would have three tables? One for Visitor, one for City, and one for VisitCity? Am I understanding the cardinality correctly?

ER Diagram

Answer Source

Yes, you described the relationships correctly. N:M cardinality needs a third table.

Visits could contain other information, too. For example, since a visitor can't be in two places at once, there might be an associated time period.

Visitor could have another relationship to City -- say, born_in with different cardinality, namely N:1, meaning many people were born in the same city (but each is born in only one city!) For that, born_in would be an attribute of Visitor.