Geek Answers Handbook

Defining a one-to-many relationship

I have some questions about the meaning of identifying relationships from one to many. I read some other related stack overflow questions, but I need a bit more info :)

Suppose we have a Country table and a Cities table. It seems to me that this is an example of a one-to-many relationship. But when I use MySql Workbench to create a one-to-one identification between the two tables, I get the following:

Countries --------- country_id (PK) ... Cities -------- city_id (PK) country_id (PK) ...

We have a composite primary key in the Cities table, and it will allow the following rows in this table (suggests that country_id and city_id are rows for better readability):

 1) France, Paris 2) England, London 3) England, Manchester 4) France, London

In order to have the correct path, we need to set the UNIQUE restriction on city_id so that it can belong to only one country. But is it not more clear to just make country_id as NOT_NULL (FK) in the Cities table and get the same effect:

 Cities --------- city_id (PK) country_id (FK) (NOT_NULL)

So, is this an identifying or non-identifying relationship? It seems to me that he “logically identifies”, but by definition he is not identifiable, since the parent PC is not part of the child PC. This is a bit confusing :)

+4
source share
3 answers

If the city key (country_id, city_id), then the relationship "identifies" - this means that the primary key partially or fully refers to an external link to another table. If country_id is not part of the primary key, then it is not identified.

These two different keys would make the table represent different things in each case, but only you can say what best suits your requirements.

Don't worry too much about the concept of identification versus non-identifying relationships. This is a concept that arises in ER modeling, but in the design of a relational database it usually has very little practical value.

+6
source

You have in your own example a counter argument for your proposal. London appears as a city in both England and France, but it is not that city; The city is identified not by city_id , which is the name of the city, but by the pair country_id , city_id . There is no other natural key to this kind of data. If you need a primary key with a single column, you will have to come up with a surrogate key to act as a primary key (say, auto-increment Integer)

+2
source

By definition, an identity association uses the primary key of a reference record.

Your last decision looks the way you need it. But this is not an identifying relationship.

+2
source

More articles:


All Articles