[SQL] Verschil Identifying en non-identifying relationships

[Storm90]

Hallo allemaal,

Ik ben bezig met een ERD in MySQL Workbench maar het is me niet echt duidelijk of er daadwerkelijk een functioneel verschil is tussen identifying en non-identifying relaties. Ik heb al rondgezocht maar ik stuit steeds op de volgende uitleg

An identifying relationship is when the existence of a row in a child table depends on a row in a parent table. This may be confusing because it's common practice these days to create a pseudokey for a child table, but not make the foreign key to the parent part of the child's primary key. Formally, the "right" way to do this is to make the foreign key part of the child's primary key. But the logical relationship is that the child cannot exist without the parent.

Example: A Person has one or more phone numbers. If they had just one phone number, we could simply store it in a column of Person. Since we want to support multiple phone numbers, we make a second table PhoneNumbers, whose primary key includes the person_id referencing the Person table.

We may think of the phone number(s) as belonging to a person, even though they are modeled as attributes of a separate table. This is a strong clue that this is an identifying relationship (even if we don't literally include person_id in the primary key of PhoneNumbers).

A non-identifying relationship is when the primary key attributes of the parent must not become primary key attributes of the child. A good example of this is a lookup table, such as a foreign key on Person.state referencing the primary key of States.state. Person is a child table with respect to States. But a row in Person is not identified by its state attribute. I.e. state is not part of the primary key of Person.

A non-identifying relationship can be optional or mandatory, which means the foreign key column allows NULL or disallows NULL, respectively.
bron: stackoverflow

Okee, deels is dat dus duidelijk. Iets dergelijks als een telefoonnummer is daadwerkelijk verbonden met een persoon en daarom wordt er voor een identifying relation gekozen. Een Staat is niet per se verbonden met een persoon (oftewel de tabel "Staten" kan Staten bevatten die niet per se zijn verbonden met een persoon).

Wat mij nu niet duidelijk is:
Als ik even een voorbeeld neem aan de Wordpress ERD, dan zie je dat daar zowel identifying relationships worden gebruikt als non-identifying. Maar de foreign-keys (rood-gekleurd ruitvormig icoon) bevatten een NOT NULL waarde. Oftewel het is nog verplicht dat een post gekoppeld is met een gebruiker, lijkt het hier op. Dat klopt toch? In dat geval kan er toch net zo goed gebruik gemaakt worden van een identifying-relationship? Of heb ik het mis?

Of is hier juist voor gekozen vanwege flexibiliteit? Dat een NOT NULL later alsnog eenvoudig naar NULL kan worden gewijzigd, indien gewenst?

[Avalaxy]

Laat ook maar gewoon.

[ Voor 255% gewijzigd door Avalaxy op 07-03-2014 15:19 ]

[Storm90]

Avalaxy schreef op vrijdag 07 maart 2014 @ 15:13:
Een identifying relationship betekent gewoon een 1 op 1 relatie. Een non-identifying relationship is een 1 op meer relatie. Een user kan dus 0, 1 of meerdere posts hebben.

Nou dat lijkt dus niet helemaal waar. Kijk maar naar de relaties:
EDIT: plaatje lijkt vaak niet te werken dus hier de bron.

Hier zie je tweemaal 1 op meer relaties. Maar de gestreepte lijn is non-identifying en de doorgetrokken streep is een identifying relatie. Het verschil is me echter niet duidelijk want in beide gevallen is het verplicht om in de child-table beide velden gevuld te hebben (de foreign key is NOT NULL). Dus daar snap ik het nut niet zo zeer van...

EDIT 2: Ik heb een bron gevonden die iets duidelijkere info verschaft:
If that relationship is identifying, we’d have the Brand Primary Key as part of the Primary Key in Products. Therefore, each Product needs a Brand to be uniquely identified.
If that relationship is non-identifying, the Brand Primary Key is still a Foreign Key in the Product Table, but it’s not part of the primary key. We can identify Products uniquely by Product ID alone.

Dus bij non-identifying relaties kan een regel in een tabel uniek geïdentificeerd worden door slechts op de primary key te zoeken, bij non-identifying heb je de waardes nodig van zowel primary key als foreign key (welke dan deel uitmaakt van de primary key). Dat verklaart dus ook waarom een tussentabel altijd een identifying-relationship nodig heeft.

Indien iemand twijfels heeft aan deze stelling, hoor ik het alsnog graag

[ Voor 46% gewijzigd door Storm90 op 07-03-2014 15:38 ]

[drm]

Volgens mij is het vooral een theoretisch verschil wat er niet zo heel veel toe doet. Maar goed, dit zou ongeveer mijn redenering zijn:

Een primary key heb je om een record te identificeren. Indien een onderdeel van die primary key verwijst naar een andere tabel, heb je dus de primary key van die andere tabel ook nodig om het child record te kunnen identificeren. Dit impliceert vervolgens dat een record niet van eigenaar kan veranderen; immers, als dat gebeurt, verandert zijn primary key en is het record dus een ander record geworden.

Anders gezegd, de eigenschap dat het record bij een record in een andere tabel hoort is onderdeel van zijn bestaansrecht, en dus meer dan een attribuut of eigenschap. Een telefoonnummer zonder daar iemand op te kunnen bereiken is ten slotte zinloos.

Een ander voorbeeld van identifying is een n:m relatie die attributen heeft. De primary key bestaan dan eigenlijk altijd uit twee foreign keys. Het record heeft geen bestaansrecht zonder aanwezigheid van de twee records waar de foreign keys naar verwijzen.

[EfBe]

Dat verhaal op stackoverflow maakt het nodeloos ingewikkeld met de opmerking dat een child niet kan bestaan zonder de parent. Dit is ook zo bij een normale non-PK, non-nullable FK van child naar parent.

Wanneer gebruik je het? Het wordt vaak gebruikt wanneer een deel van de attributes van een entity optional zijn, en die worden dan in een aparte table gemodeled: middels de PK van de aparte tabel is de row verbonden met de parent row. De aparte tabel wordt gekozen wanneer de nullable fields niet vaak gevuld zijn waardoor je wat winst haalt. Tegenwoordig is dit niet echt meer zo nuttig: diskspace is niet schaars en de normalizatie zorgt ervoor dat je queries trager zijn.

Ook wanneer je Target per entity inheritance wil modelen in een relational model gebruik je dit. Er kunnen extra fields in de childtable's PK zitten waardoor je een 1:n relatie krijgt tussen parent en child.

[Storm90]

Volgens mij is het vooral een theoretisch verschil wat er niet zo heel veel toe doet. Maar goed, dit zou ongeveer mijn redenering zijn:

Een primary key heb je om een record te identificeren. Indien een onderdeel van die primary key verwijst naar een andere tabel, heb je dus de primary key van die andere tabel ook nodig om het child record te kunnen identificeren. Dit impliceert vervolgens dat een record niet van eigenaar kan veranderen; immers, als dat gebeurt, verandert zijn primary key en is het record dus een ander record geworden.

Anders gezegd, de eigenschap dat het record bij een record in een andere tabel hoort is onderdeel van zijn bestaansrecht, en dus meer dan een attribuut of eigenschap. Een telefoonnummer zonder daar iemand op te kunnen bereiken is ten slotte zinloos.

Een ander voorbeeld van identifying is een n:m relatie die attributen heeft. De primary key bestaan dan eigenlijk altijd uit twee foreign keys. Het record heeft geen bestaansrecht zonder aanwezigheid van de twee records waar de foreign keys naar verwijzen.

Bedankt voor de uitgebreide uitleg Het is me nu gelukkig al een stuk duidelijker. Ook dankzij de link die ik eerder benoemde. Maar zoals je al zei is het verschil voornamelijk theoretisch, dat is ook mijn mening.

Dit is ook zo bij een normale non-PK, non-nullable FK van child naar parent.

Klopt exact, daar ontstond ook mijn verwarring, zie OT.

Wanneer gebruik je het? Het wordt vaak gebruikt wanneer een deel van de attributes van een entity optional zijn, en die worden dan in een aparte table gemodeled: middels de PK van de aparte tabel is de row verbonden met de parent row. De aparte tabel wordt gekozen wanneer de nullable fields niet vaak gevuld zijn waardoor je wat winst haalt. Tegenwoordig is dit niet echt meer zo nuttig: diskspace is niet schaars en de normalizatie zorgt ervoor dat je queries trager zijn.

Nou voor dat laatste gebruik ik die in ieder geval niet Op dit moment heb wel aparte tabellen die echt noodzakelijk zijn, welke gevuld zullen worden met ontelbare hoeveelheden child records (die je dus niet in één tabel kwijt kan) Maar goed om te weten dat een aparte tabel voor fields die vaak leeg zijn, tegenwoordig niet altijd verstandig is! Zeker logisch maar niet iets waar je direct aan zou denken.

[ Voor 5% gewijzigd door Storm90 op 10-03-2014 10:26 ]