sql server - What Kind of Relationship is Between These Tables?

Question

I have two tables that have foreign keys to each other's primary key. This DB is in French. I will translate the two tables that I want to you to understand.

• Atelier Cuisine ==> Kitchen
• Cuisinier == > Cooking chef

So in this picture we see that in the Kitchen table we have a PK referenced by the FK from the Cooking chef table; in the Cooking chef table we have a PK referenced by the FK from the Kitchen table. So I am confused. I don't understand this kind of relationship between these tables.

And I hope to check my query that I did to create these two tables if its correct

CREATE TABLE [ATELIER CUISINE] ( 

NumCuisine INT NOT NULL PRIMARY KEY,
TelCuisine VARCHAR(50) NOT NULL
)

CREATE TABLE CUISINIER (

NumCuisinier  INT NOT NULL PRIMARY KEY,
NomCuis  VARCHAR(50) NOT NULL,
DateEmb DATE NOT NULL,
NumCuisine INT NOT NULL CONSTRAINT FK_CUISINIER_NumCuisine FOREIGN KEY REFERENCES [ATELIER CUISINE](NumCuisine)

See the Image here:
Relationship model of the restaurant database

See the Image here:
Example records for some tables

Answer 1

What you are seeing here is most probably the most important child pattern.

I would assume that we need to start from the standard parent-child relationship of "one kitchen employs one-or-more chefs". So we would have a foreign key in the cuisinier / chef table that contains the ID of the atelier_cuisine / kitchen where the cuisinierin question works.

But for some reason, the kitchen must be able to point at the most important cook/chef working there. Only that numCuisinier or chefID / cookID is a poor naming of that foreign key column, as it misleads, just as it mislead us in your example. If you gave it a name like numCuisinierChef or "chef/cook in chief ID", the column name would be self-explanatory.

But, the way the columns were named here, it could also be just the other way around: a chef/cook works in one or more kitchens, but one kitchen is his or her most important employer.

Without a documentation or comments in the data model, you're fried, really ...

Hope this helps rather than confuses - as the data model and the naming does ...

Maybe one clue. MarceloBarbosa actually inspired me to that: If , in atelier_cuisine , numCuisinier is null-able, and , in cuisinier, numCuisine is NOT NULL, then atelier_cuisine is the parent, and cuisinier is the child, and vice-versa.

Marco the Sane