Samengestelde sleutel of Identifier

[Wushi]

Hi,

Ik ben student ben bezig een paper te schrijven over Data Quality. In een boek dat ik aan het lezen ben, staat dat duplicaten in relationele databases worden vermeden met samengestelde sleutels. Maar nu is mijn vraag of samengestelde sleutels wel gebruikt worden in de business, aangezien de voordelen bij identifiers vele groter zijn.

Wat gebruiken ze in jullie bedrijf? Samengestelde sleutels of identifiers? Of wat is jullie mening hierover?

Het zou een grote hulp zijn!

[RobIII]

Wushi schreef op vrijdag 10 december 2010 @ 00:23:
Wat gebruiken ze in jullie bedrijf? Samengestelde sleutels of identifiers? Of wat is jullie mening hierover?

Het is niet het één of het ander. De ene situatie vraagt om compound keys, de andere om synthetic keys.

Wushi schreef op vrijdag 10 december 2010 @ 00:23:
aangezien de voordelen bij identifiers vele groter zijn

Noem ze eens?

[ Voor 31% gewijzigd door RobIII op 10-12-2010 00:31 ]

[Verwijderd]

Leg eens uit waarom toevoegen van een id (neem aan dat je even surrogate key bedoelt) voordelen biedt? Je zou het kunnen gebruiken, maar je zult nog steeds een unique constraint moeten hebben over alle kolommen die anders samen de primary key zouden zijn.

Het kan handig zijn om een "simpelere" verwijzing te hebben naar een bepaald record, maar het is dus vooral een kwestie van gemakzucht. In een goed genormaliseerde database zou je compound keys gebruiken. Soms is het echter praktischer daar licht van af te wijken. Een kwestie van smaak en kunde.

[Jrz]

Nope.
Business logica en entiteiten veranderen. Een primary key kan NOOIT veranderen.
Als je foreign keys wil updaten, is een surrogate key wat handiger, dan 3 velden updaten.

[ Voor 5% gewijzigd door Jrz op 10-12-2010 00:39 ]

[Wushi]

Noem ze eens?

Vele groter was misschien overdreven, maar zoals Jrz al aanhaalde, business logic verandert continue dus zou de business key ook continue aangepast moesten worden + alle verwijzingen in de databank. Terwijl een Surrogaat Key (of identifier voor een bepaald record) ongewijzigd blijft.

Het enigste voordeel dat ik zie bij business keys (of compound key) is dat er plaats in de databank wordt bespaard, omdat we geen extra veld moeten nemen.

@Jzr, hoe wordt dan duplicatie in de tabel vermeden? Kandidaat sleutel opstellen?

[RobIII]

Wushi schreef op vrijdag 10 december 2010 @ 09:38:
[...]


Vele groter was misschien overdreven, maar zoals Jrz al aanhaalde, business logic verandert continue dus zou de business key ook continue aangepast moesten worden

Business key

+ alle verwijzingen in de databank. Terwijl een Surrogaat Key (of identifier voor een bepaald record) ongewijzigd blijft.

Als je een compound key van 2 surrogaat keys hebt blijft dat principe toch staan?

Het enigste voordeel dat ik zie bij business keys (of compound key) is dat er plaats in de databank wordt bespaard, omdat we geen extra veld moeten nemen.

Ruimte is wel je minste probleem. Leg me eens uit wat het voordeel van een compound key waaraan je weer een surrogaat key hangt boven "gewoon" een compound key is ?

@Jzr, hoe wordt dan duplicatie in de tabel vermeden? Kandidaat sleutel opstellen?

Ik kan sowieso weinig hout snijden van Jzr's post. Wat verduidelijking zou niet misstaan imho.

[Wushi]

RobIII schreef op vrijdag 10 december 2010 @ 09:41:
[...]

Business key

Ik gebruik ook alleen maar de terminologie die ze ons aanleren

Business Key is een uniek veld, die een betekenis heeft voor het bedrijf en de klant (bv product serienummer).

Bij nader inzien was ik even in de war, want dacht dat deze meestal ook werd samengesteld met meerdere kolommen uit tabel, maar wordt ook als surrogaat key gebruikt

RobIII schreef op vrijdag 10 december 2010 @ 09:41:
[...]

Als je een compound key van 2 surrogaat keys hebt blijft dat principe toch staan?

Een compound key van 2 surrogaat keys wel, maar je hebt ook compound keys van velden waarvan de combinatie uniek is. Als er bijvoorbeeld in een van die velden iets veranderd, veranderd de compound key mee, waardoor alle verwijzigingen naar dat record ook geupdate moeten worden.

RobIII schreef op vrijdag 10 december 2010 @ 09:41:
[...]

Ruimte is wel je minste probleem. Leg me eens uit wat het voordeel van een compound key waaraan je weer een surrogaat key hangt boven "gewoon" een compound key is ?

Akkoord ivm ruimte, dit is zelfs verwaarloosbaar als "voordeel". Net zoals hierboven, een onafhankelijk ID als key gebruiken kan / hoeft niet mee geupdate te worden. Dit wordt ook meerdere malen in cursus Databanken dat herhaald compound keys liefst vermeden worden omwille van deze reden (denormaliseren).

RobIII schreef op vrijdag 10 december 2010 @ 09:41:
[...]

Ik kan sowieso weinig hout snijden van Jzr's post. Wat verduidelijking zou niet misstaan imho.

Denk dat Jzr hetzelfde als mij bedoelt.

[Janoz]

Vergeef RobIII, die komt uit limburg

@RobIII Kom kom, je weet het wel. Business key is de uniek identificerende sleutel van een entiteit volgens het functioneel ontwerp. Denk daarbij bij mensen bv aan hun BSN en bij bedrijven aan hun KVKnr. Echter kan dit ook een samengestelde sleutel zijn.

Dit is allemaal leuk en aardig voor het functioneel ontwerp, maar technische gebruik ik altijd een surrogaat key/identifier, echter komt er op de businesskey wel een unique constraint! Samengestelde sleutels gebruik ik ook wel, maar die zijn dan wel samengesteld uit surrogaat keys (denk aan koppeltabellen).

[RobIII]

Janoz schreef op vrijdag 10 december 2010 @ 10:40:
Vergeef RobIII, die komt uit limburg

Janoz schreef op vrijdag 10 december 2010 @ 10:40:
@RobIII Kom kom, je weet het wel. Business key is de uniek identificerende sleutel van een entiteit volgens het functioneel ontwerp.

Die term is mij iig nooit geleerd (En wikipedia kent 'm ook niet als zodanig). Maar het was me inmiddels wel aan 't dagen Ik weet niet beter dan dat 't Natural key (of domain key) heet

Janoz schreef op vrijdag 10 december 2010 @ 10:40:
Dit is allemaal leuk en aardig voor het functioneel ontwerp, maar technische gebruik ik altijd een surrogaat key/identifier

Idem hier.

Janoz schreef op vrijdag 10 december 2010 @ 10:40:
echter komt er op de businesskey wel een unique constraint!

Juist; of (eventueel) een andere constraint (verzin iets; de businesskey moet altijd deelbaar door 7 zijn ofzo).

Janoz schreef op vrijdag 10 december 2010 @ 10:40:
Samengestelde sleutels gebruik ik ook wel, maar die zijn dan wel samengesteld uit surrogaat keys (denk aan koppeltabellen).

Psies. Either way: het is dus (zie topictitel) niet of/of maar en/of. Ik heb zat projecten waarin beide voorkomen. En zoals ik al zei: het is maar net waar de situatie om vraagt.

[ Voor 8% gewijzigd door RobIII op 10-12-2010 10:51 ]

[Janoz]

RobIII schreef op vrijdag 10 december 2010 @ 10:48:
Die term is mij iig nooit geleerd (En wikipedia kent 'm ook niet als zodanig). Maar het was me inmiddels wel aan 't dagen Ik weet niet beter dan dat 't Natural key (of domain key) heet

Je bent nooit te oud om te leren . Ik ben de term iig regelmatig tegen gekomen.

Psies. Either way: het is dus (zie topictitel) niet of/of maar en/of. Ik heb zat projecten waarin beide voorkomen. En zoals ik al zei: het is maar net waar de situatie om vraagt.

Met het gevaar dat ik misschien teveel aanneem mbt de vraag van de topicstarter denk ik toch dat de werkelijke vraag van de topicstarter is:
(evt samengestelde) Businesskeys gebruiken
vs
Surrogaat keys gebruiken.

imho is dat zeker geen 'en'.

[RobIII]

Janoz schreef op vrijdag 10 december 2010 @ 10:53:
imho is dat zeker geen 'en'.

Volgens mij lullen we langs elkaar
Ik zeg dan ook:
Surrogaatkeys->Yes (zo goed als altijd, uitzonderingen bevestigen de regel though)
Compound->Yes (mits 2 (of meer) surrogaatkeys).
Natural keys->Nope (zo goed als nooit, uitzonderingen bevestigen de regel though)

[ Voor 11% gewijzigd door RobIII op 10-12-2010 11:04 ]

[djluc]

Stel er zit een tikfout in het serienummer. Die kan je bij een business key niet meer veranderen of heel lastig (alle references controleren en aanpassen). Bij een surrogaat key kan dat wel gewoon. Dan kan je in je business laag de noodzakelijke controles en beperkingen toepassen maar je data model beperkt je in elk geval niet. Daarom werken wij vrijwel altijd met surrogaat keys.

Daarnaast zijn veel frameworks ook gebaseerd op het werken met een ID kolom. Dat is een beetje de standaard dus wel handig en gebruikelijk. Een ID wijzigen: nooit.

Verwijderd schreef op vrijdag 10 december 2010 @ 00:30:
Leg eens uit waarom toevoegen van een id (neem aan dat je even surrogate key bedoelt) voordelen biedt? Je zou het kunnen gebruiken, maar je zult nog steeds een unique constraint moeten hebben over alle kolommen die anders samen de primary key zouden zijn.

Het kan handig zijn om een "simpelere" verwijzing te hebben naar een bepaald record, maar het is dus vooral een kwestie van gemakzucht. In een goed genormaliseerde database zou je compound keys gebruiken. Soms is het echter praktischer daar licht van af te wijken. Een kwestie van smaak en kunde.

Cheetag heeft absoluut een punt, toch vind ik het een lastige. Vaak zie je het niet gebeuren, vaak zie je toch de ID kolom. Gezien wat ik bovenstaand vermeld vind ik dat ook de meest logische keuze. Als je met frameworks e.d. werkt die zo'n samengestelde key wel kunnen verwerken vind ik de keuze wel logisch echter. Je hebt al de logische key dus waarom nog eentje toevoegen?

Persoonlijk moet de data consistent blijven wat mij betreft maar wat flexibiliteit is ook niet verkeerd zolang het maar strak gecontroleerd wordt in de business laag en met de validaties op de data uiteraard.

[Janoz]

RobIII schreef op vrijdag 10 december 2010 @ 10:56:
Volgens mij lullen we langs elkaar

Nee hoor, nu niet meer

[KabouterSuper]

Ik heb behoorlijk lang gewerkt aan een financiele software. Daar was het credo: surrogaat keys overnemen van de bronsystemen. Het is namelijk geheel overbodig om isin-codes, iso-country codes, etc,etc opnieuw een identifier te geven.

Daarbij werd het erg gewaardeerd door onze klanten om keys op basis van varchar's te maken in plaats van integers...."aandeel 12345673 van klant 982 heeft een marktwaarde van 1Mln in currency 8" leest nou eenmaal minder lekker dan "aandeel NL0000387058 van klant RIJKE_STINKERD heeft een marktwaarde van 1Mln in EUR". Ik weet best dat het iets langzamer is qua queries, en dat een goede front-end altijd omschrijvingen erbij kan querien, maar in de praktijk is het verrekte praktisch als je in je database lowlevel kunt querien.

[Jrz]

Arg.. Jzr.. Jrz ;-)

* Een primary key kan geen null kolom bevatten.

* Hoe ga je een kolom toevoegen? bijv: PK = (A,B) => PK = (A,B,C)

* Hoe ga je je PK updaten als ze gereferenced worden?

[Jrz]

KabouterSuper schreef op vrijdag 10 december 2010 @ 11:19:
Ik heb behoorlijk lang gewerkt aan een financiele software. Daar was het credo: surrogaat keys overnemen van de bronsystemen. Het is namelijk geheel overbodig om isin-codes, iso-country codes, etc,etc opnieuw een identifier te geven.

Daarbij werd het erg gewaardeerd door onze klanten om keys op basis van varchar's te maken in plaats van integers...."aandeel 12345673 van klant 982 heeft een marktwaarde van 1Mln in currency 8" leest nou eenmaal minder lekker dan "aandeel NL0000387058 van klant RIJKE_STINKERD heeft een marktwaarde van 1Mln in EUR". Ik weet best dat het iets langzamer is qua queries, en dat een goede front-end altijd omschrijvingen erbij kan querien, maar in de praktijk is het verrekte praktisch als je in je database lowlevel kunt querien.

ISO codes etc ben ik het inderdaad mee eens.
Vooral bij financiele / legacy systemen zie je dat ze nog met dat soort keys werken. Ik heb ook een tijd in de verzekeringsbranche gewerkt, en daar is het hetzelfde verhaal.

[djluc]

KabouterSuper schreef op vrijdag 10 december 2010 @ 11:19:
Ik heb behoorlijk lang gewerkt aan een financiele software. Daar was het credo: surrogaat keys overnemen van de bronsystemen. Het is namelijk geheel overbodig om isin-codes, iso-country codes, etc,etc opnieuw een identifier te geven.

Daarbij werd het erg gewaardeerd door onze klanten om keys op basis van varchar's te maken in plaats van integers...."aandeel 12345673 van klant 982 heeft een marktwaarde van 1Mln in currency 8" leest nou eenmaal minder lekker dan "aandeel NL0000387058 van klant RIJKE_STINKERD heeft een marktwaarde van 1Mln in EUR". Ik weet best dat het iets langzamer is qua queries, en dat een goede front-end altijd omschrijvingen erbij kan querien, maar in de praktijk is het verrekte praktisch als je in je database lowlevel kunt querien.

Ik zie de link tussen weergave en datamodel echt niet, dat is een kwestie van programmeren.

Iets wat een standaard code is zoals een ISO kan ik wel inkomen. Al zou ik daar ook nog over twijfelen, bijvoorbeeld als je toch beschrijvingen e.d. er bij wilt hebben etc. Maar dat is pure luiheid, liever alles met 1 logica volgens een standaard framework o.i.d. dan een uitzondering voor bijvoorbeeld ISO codes.

[RobIII]

djluc schreef op vrijdag 10 december 2010 @ 12:11:
Iets wat een standaard code is zoals een ISO kan ik wel inkomen. Al zou ik daar ook nog over twijfelen

En terecht. ISO codes wijzigen ook _{(net als postcodes overigens, dat kwam ik ook eens tegen als PK (i.c.m. huisnummer) )} en vaker dan je denkt. En dat is niet alles: vervallen codes worden soms zelfs weer in (her)gebruik genomen en dat kan prima een compleet nieuwe betekenis hebben dan. Doe mij maar een surrogaat key dan.

Voorbeeldje

[ Voor 11% gewijzigd door RobIII op 10-12-2010 12:40 ]

[Wushi]

ISO codes ed zijn ook "stabiele" data (eventjes tussen aanhalingstekens gezet, want de kans bestaat altijd dat het veranderd, al is dit eerder zeldzaam) die als business key gebruikt kunnen worden. In deze tabellen is de kans dat er duplicaten ontstaan ook eerder miniem.

Maar in tabellen met bv klantgegevens veranderen continue gegevens, waardoor een (samengestelde) business key niet praktisch is. En juist in deze tabellen ontstaan er duplicaten. Maar zoals hier werd aangehaald kan men beter een unique constraint gebruiken voor dit probleem.

Ik neem aan dat de business dus vooral gebruik maakt van surrogaat keys / identifiers, dus mijn vermoedens werden bevestigd.

Wel interessant dit onderwerp te volgen

[RobIII]

Wushi schreef op vrijdag 10 december 2010 @ 12:56:
ISO codes ed zijn ook "stabiele" data

Dat kaart ik net aan: zo "stabiel" zijn die niet (natuurlijk afhankelijk van wat je "stabiel" noemt). Natuurlijk ook afhankelijk over wélke ISO codes we het hebben (er zijn er talloze) maar bijvoorbeeld landen wijzigen dus met "regelmaat". Elke keer als er ergens de pleuris uit breekt en een landje een ander overneemt of een landje opgesplitst wordt in 2 nieuwe landen/staten heb je al een wijziging. Dat de ISO2 code voor Nederland, Duitsland of België stabiel is ben ik met je eens; heb je echter de hele shebang aan iso codes in een tabel zitten dan ga je die dus bij moeten gaan houden (en dan kun je er dus maar beter, zoals ik aangeef, een surrogaat key aan hangen).

_{Voor postcodes zijn 't (en die hoeveelheid verbaasde me nog aardig indertijd): 30.000 mutaties per jaar}

[ Voor 73% gewijzigd door RobIII op 10-12-2010 13:07 ]

[Wushi]

Weet ik, ik moet eens leren te refreshen voor ik reageer

Stabiele data zijn waarden die nooit veranderen bv geboortedatum, maar zoals ik al in vorige post zei, ISO-codes veranderen wel, maar niet zo regelmatig als andere data

[Remus]

djluc schreef op vrijdag 10 december 2010 @ 11:03:
Stel er zit een tikfout in het serienummer. Die kan je bij een business key niet meer veranderen of heel lastig (alle references controleren en aanpassen). Bij een surrogaat key kan dat wel gewoon. Dan kan je in je business laag de noodzakelijke controles en beperkingen toepassen maar je data model beperkt je in elk geval niet. Daarom werken wij vrijwel altijd met surrogaat keys.

Hoewel niet perfect, heb je voor dit soort situaties natuurlijk 'ON UPDATE CASCADE' clauses in DDL; je hoeft - bij een goed opgezette database (op een DBMS met support voor CASCADE) - foreign key references dan dus niet handmatig aan te passen.

Het enige probleem is dat je - tenzij je je databasemodel en het domein door en door kent - nooit helemaal zeker kan zijn of een dergelijke update faalt (omdat er ergens een ON UPDATE RESTRICT staat), of voor onverwachte domein impact zorgt.

Wat dat betreft zijn surrogate keys toch echt te verkiezen boven natural keys. Daarnaast zijn indices op integers toch een stuk minder groot dan indices op (VAR)CHAR, al is dat op zich geen reden om voor surrogate keys te gaan, lijkt het mij in voorkomende gevallen toch ook een voordeel.

[Janoz]

Wushi schreef op vrijdag 10 december 2010 @ 13:03:
Weet ik, ik moet eens leren te refreshen voor ik reageer

Stabiele data zijn waarden die nooit veranderen bv geboortedatum, maar zoals ik al in vorige post zei, ISO-codes veranderen wel, maar niet zo regelmatig als andere data

Ik heb in mijn huidige project en in mijn vorige project vaak te maken met GBA gegevens. Ik kan alvast melden dat geboortedatum geen stabiele waarde is.

Of data stabiel is is vaak gebaseerd op aannames. Helaas is in computer land (itt normalemensentaal) "zo goed als bijna nooit want het is ook nog nooit voorgekomen" niet hetzelfde als "nooit".

[Remus]

Wushi schreef op vrijdag 10 december 2010 @ 13:03:
Weet ik, ik moet eens leren te refreshen voor ik reageer

Stabiele data zijn waarden die nooit veranderen bv geboortedatum, maar zoals ik al in vorige post zei, ISO-codes veranderen wel, maar niet zo regelmatig als andere data

Nee, mensen maken nooit invoerfouten of geven nooit verkeerd informatie op , of medewerkers vullen nooit verzonnen informatie in, omdat het systeem ze dwingt iets in te vullen, terwijl het proces dergelijke informatie niet/nooit op dat punt beschikbaar maakt.

Toen ik 18 of 19 jaar geleden aan een krantenwijk begon (op mijn 12e of 13e), werd ik door de agent ingeschreven als zijnde 16. Op een gegeven moment ging de krant over naar een ander bedrijf en werd er gevraagd om de gegevens te controleren. Ik heb toen doodleuk mijn geboortedatum gecorrigeerd.

[SPee]

Zoals bij ieder domein model moet hier (goed) over nagedacht worden. En soms wordt het ene gebruikt, terwijl het andere handiger is.

Zo werkte ik een keer aan een applicatie die een Primary Key had van: startdatum, einddatum, categorie, product. Die had alleen een samengestelde ID en niet nog een nummertje. Leuk als dat in 10 tabellen op dezelfde manier voorkomt. Dat zorgt ervoor dat het heel vervelend is om de relaties tussen de tabellen te definieren (zeker als die er in de db niet zijn ). Het gebruiken van een Id was hier handiger geweest.

Andersom kan ook. Een ordernummer in UUID (uniek) dat als veld wordt gebruikt, terwijl het prima als PK gebruikt had kunnen worden. Bij het zoeken op het ordernummer moet dat tabel dus worden meegenomen.

Dus elk gebruik heeft zijn voordeel en nadeel en moet doordacht worden. Het is dus niet eenvoudig om te zeggen dat het ene beter is dan het andere. Het gebruiken van een Identifier heeft wel vaker minder nadelen dan een samengestelde sleutel, dus zal hier ook vaak voor gekozen worden. Zeker als de business key meerdere velden gebruikt.

[Crazy D]

Remus schreef op vrijdag 10 december 2010 @ 13:07:
Wat dat betreft zijn surrogate keys toch echt te verkiezen boven natural keys. Daarnaast zijn indices op integers toch een stuk minder groot dan indices op (VAR)CHAR, al is dat op zich geen reden om voor surrogate keys te gaan, lijkt het mij in voorkomende gevallen toch ook een voordeel.

Al zul je in veel gevallen ook een index willen op de werkelijke key (zoals bv de artikelcode) waarmee dat voordeel weer ongedaan wordt gemaakt, maar het is idd een leuke meevaller

[KabouterSuper]

Toen ik in Oracle werkte, werd ik trouwens wel behoorlijk moe van alles wat je moest doen om een surrogate key te maken:
1) sequence aanmaken
2) trigger op de tabel die een sequence-nummertje pakt
3) unique key op de juiste velden

Vooral de tweede was naar, omdat je dit wel foolproof wilt doen (bestaan van sequence, consistentie van sequence met tabel, etc).

Wat betreft het wijzigen van iso-codes, isin-codes, etc....dit is inderdaad waar, maar werd eigenlijk altijd in de bronsystemen al opgelost. In de praktijk hoefde je je dus geen zorgen te maken over dit soort mutaties.

offtopic:
Op de tu-delft werden sommige gegevens op basis van studentnr bijgehouden, maar sommige ook op basis van achternaam en geboortedatum....heel fijn als je een tweelingbroer hebt die ook in Delft studeert.

[cariolive23]

KabouterSuper schreef op zaterdag 11 december 2010 @ 07:57:
Toen ik in Oracle werkte, werd ik trouwens wel behoorlijk moe van alles wat je moest doen om een surrogate key te maken:
1) sequence aanmaken
2) trigger op de tabel die een sequence-nummertje pakt
3) unique key op de juiste velden

De complexiteit verschilt enorm per merk database en ook nog eens per tool die jij gebruikt voor het maken van een datamodel: Vele tools maken voor jou de benodigde SQL aan, dan kunnen ze het ook direct goed doen.

In PostgreSQL en MySQL stelt het niks voor, een SERIAL en een AUTO_INCREMENT doen wonderen:
PostgreSQL:

SQL:

CREATE TABLE foo(
  id SERIAL PRIMARY KEY,
  bar VARCHAR(20) UNIQUE
);

MySQL:

SQL:

CREATE TABLE foo(
  id INT AUTO_INCREMENT PRIMARY KEY,
  bar VARCHAR(20) UNIQUE
);

De unique constraints op één of meer overige kolommen, komen uit je datamodel rollen, die hebben op zich niets te maken met een surrogate key. Het is de surrogate key die je extra moet aanmaken en foreign key constraints die wijzen naar de diverse surrogate keys i.p.v. de natural keys. That's all.

[glrfndl]

Een voordeel van een surrogate key is dat je indexen (en dus je database) minder groot kunnen zijn dan bij een natural key. Dit is natuurlijk afhankelijk van de grootte van je natural key, maar als die uit een aantal kolommen bestaat met ook nog eens grote datatypes dan kan het absoluut lonen om een surrogate key te introduceren. Als je primary key (er van uit gaande dat die natural key je primary key wordt) dan ook nog je clustered index key is (default in mssql en mysql, hoewel je het in mssql wel kunt overriden), dan telt die grootte ook nog eens door in eventuele non-clustered indexes. Bij kleine databases zul je het niet zo gauw merken, maar de overhead (in diskspace) die je creëert bij grotere databases kan aardig oplopen.

[Verwijderd]

Foute conclusie. Als je een surrogate key introduceert heb je een extra index. Over de velden die anders de primary key zouden vormen, heb je nog steeds een unique index nodig.

De winst die je boekt met een surrogate key zal dus niet in schijfruimte zitten, integendeel. De winst moet komen van snelheid of gemak.

[glrfndl]

Verwijderd schreef op woensdag 15 december 2010 @ 08:17:
Foute conclusie. Als je een surrogate key introduceert heb je een extra index. Over de velden die anders de primary key zouden vormen, heb je nog steeds een unique index nodig.

De winst die je boekt met een surrogate key zal dus niet in schijfruimte zitten, integendeel. De winst moet komen van snelheid of gemak.

Door een surrogate key te gebruiken kan het zijn dat je indexen veel kleiner blijven. Uiteraard afhankelijk van hoe je tabellen en indexen er uit zien (it all depends). En all things being equal, kun je sneller door 1MB data zoeken dan door 10MB. Dus ja, winst komt uit snelheid, maar die snelheidswinst wordt (oa) veroorzaakt doordat je minder data hebt. Het mes snijdt ook nog aan 2 kanten: een kleinere index is niet alleen sneller te doorzoeken, als je indexen kleiner zijn passen er ook nog eens meer in het RAM.

[Verwijderd]

Maar je gebruikt dan wel 11 MB aan opslagruimte, niet 1 MB of 10 MB

[glrfndl]

Ik ga nu uit van MS SQL server, maar nee, met een surrogate key _kan_ je totale index size dus veel kleiner blijven dan wanneer je een natural key als primary key gebruikt. Maar het hangt er allemaal vanaf hoe je je indexen maakt. Stel je hebt een tabel met personen en een natural key die er als volgt uit ziet (just for the sake of it).

- voornaam nvarchar(50)
- achternaam nvarchar(50)
- geslacht char(1)
- geboortedatum datetime

Je definieert hierover je primary key, en dit wordt in mssql default dan ook je clustered index (dit kun je overriden). Dat is een vrij brede key dus.

Daarnaast heb je op deze tabel nog een aantal andere indexen op andere kolommen, bijv. op telefoonnummer, woonplaats, huisdiernaam, etc. Dit worden dus non-clustered indexes, maar de clou is dat sowieso elk leaf-level in elke non-clustered index die clustered key meeneemt. Oftewel, hoe breder je clustered key, hoe groter je non-clustered indexes worden en zie daar je winst als je een clustered index op een surrogate int veld van slechts 4 bytes zet.

Nogmaals, dit is hoe het in Microsoft SQL server werkt. En bij kleine databases zal het niet zo'n impact hebben, maar bij grotere databases kan het dus wel degelijk aardig oplopen.

[Janoz]

Mooie surrogate key. Ik kan je alvast vertellen dat er iemand is die op dezelfde dag geboren is als mij en die ook dezelfde naam heeft. En ja, dat is ook een man.

@Rob hieronder: Staan we weer quite

[ Voor 13% gewijzigd door Janoz op 15-12-2010 14:23 ]

[RobIII]

Janoz schreef op woensdag 15 december 2010 @ 12:27:
Mooie surrogate "natural" key. Ik kan je alvast vertellen dat er iemand is die op dezelfde dag geboren is als mij en die ook dezelfde naam heeft. En ja, dat is ook een man.

Fixed that for you

[glrfndl]

Kom op, het gaat er toch niet om of dit nou een handige key is. Er werden vragen gesteld wat de voordelen zouden zijn van een surrogate key. Ik laat zien wat er gebeurt als je zo'n brede (natural) key hebt en wat voor nadelen daar aan hangen en waarom een surrogate key voordelen op kan leveren.

Een ander nadeel van een natural key _kan_ trouwens zijn dat je veel meer index fragmentation krijgt. Maar ook hier hangt het er vanaf hoe je key er dan uitziet. It all depends...

[RobIII]

glrfndl schreef op woensdag 15 december 2010 @ 13:58:
Ik laat zien wat er gebeurt als je zo'n brede (natural) key hebt

Nee, je laat zien wat een (ietwat knullige, maar for the sake of it inderdaad) compound natural key voor nadeel zou hebben Een beter voorbeeld was geweest een BSN (Sofi-Nummer) als (Natural) PK. En een BSN zou inderdaad meer "index space" in beslag nemen dan een (al dan niet auto inc) integer en idd meer index fragmentation veroorzaken (los van de nadelen als "duplicate BSN's" _{die niet zouden moeten bestaan maar 't zou me ook niets verbazen} )

[ Voor 28% gewijzigd door RobIII op 15-12-2010 14:04 ]

[glrfndl]

Het doet er op zich niet toe of je natural key compound is of niet, het gaat er om hoeveel bytes groot hij is, zie je voorbeeld van BSN nummer. Maar omdat hier vooral gesproken werd over compound keys heb ik dat ook maar als voorbeeld genomen.

Edit: oh ja, dingen die niet zouden moeten kunnen, altijd leuk... Hoe vaak ik op mijn werk ook wel niet verschillende personen met hetzelfde BSN nummer voorbij zie komen

[ Voor 26% gewijzigd door glrfndl op 15-12-2010 14:20 ]

[CyBeRSPiN]

Heb zelf ervaring met een groot systeem waarbij we beide toepassen, bijna elke tabel een technische ID kolom, maar voor FK's is er de voorkeur om de 'logische' sleutels te gebruiken (het logische gegevensmodel kent vaak geen 'ID', voor de klant betekent de kolom niets).
Twee voordelen van het gebruik van betekenisvolle sleutels: je kunt de data in een tabel veel simpeler interpreteren en je hebt veel minder joins nodig omdat de FK zelf al de info bevat.

Met samengestelde sleutels is er overigens wel een gevaar dat je een kolom vergeet en daardoor ongewenst teveel data ophaalt, een standaard voor de naamgeving van FK kolommen is dan ook onontbeerlijk.

De logische sleutels zijn niet updatable, hoewel dat met een fatsoenlijk DBMS volgens mij nog wel te doen is (on update cascade) is dat wel vragen om problemen..

[cariolive23]

CyBeRSPiN schreef op woensdag 15 december 2010 @ 21:11:De logische sleutels zijn niet updatable, hoewel dat met een fatsoenlijk DBMS volgens mij nog wel te doen is (on update cascade) is dat wel vragen om problemen..

"on update cascade" bestaat al vele jaren en wordt door vele databases ondersteund, maar je kunt bij een update van een sleutel wel een flinke performance dip krijgen. Het updaten van één record kan er dan voor zorgen dat er (afhankelijk van het datamodel) vele tientallen of zelfs honderden records in verschillende tabellen moeten worden bijgewerkt. Wanneer een update van een sleutel zelden of nooit voorkomt, dan hoeft de uitzondering geen probleem te zijn. Het is wel iets waar performance problemen voorspelbaar zijn, je kunt dus een goed overwogen keuze maken.

Zorg er wel voor dat anderen ook weten waarom de keuze voor een bepaalde oplossing is gemaakt, dat scheelt weer een hoop gemiep.