[SQL] Surrogate key of niet?

[DPLuS]

Momenteel ben ik bezig met een online rooster-systeem.
Hier kan men voor bepaalde afdelingen binnen een bedrijf diensten definiëren en die diensten kunnen weer op een bepaalde datum toegekend worden aan een werknemer.

De tabel Diensten ziet er als volgt uit:

code:

mysql> describe Diensten;
+-------------+-----------------+------+-----+---------+----------------+
| Field       | Type            | Null | Key | Default | Extra          |
+-------------+-----------------+------+-----+---------+----------------+
| dienst_id   | int(3) unsigned | NO   | PRI | NULL    | auto_increment | 
| afdeling_id | int(3) unsigned | NO   |     |         |                | 
| token       | varchar(3)      | NO   | MUL |         |                | 
| description | varchar(50)     | NO   |     |         |                | 
| start_time  | time            | NO   |     |         |                | 
| end_time    | time            | NO   |     |         |                | 
| bg_color    | char(7)         | NO   |     |         |                | 
| color       | char(7)         | NO   |     |         |                | 
+-------------+-----------------+------+-----+---------+----------------+

dienst_id is in dit geval de primary key,
afdeling_id is een FK voor de afdeling waarvoor deze dienst van toepassing is,
token is een 3-letter combinatie die men in het rooster-overzicht te zien krijgt ter identificatie van deze dienst (bijv. RDD voor Reguliere Dag Dienst),
description is de beschrijving van de dienst,
start_time en end_time vertegenwoordigen de starttijd en eindtijd voor de dienst,
bg_color en color zijn waardes die in de CSS (stylesheet) van toepassing zijn voor deze dienst.


Nu heb ik daar de volgende vraag over:

In principe zou de combinatie afdeling_id/token ook voldoen als primary key, maar ik heb begrepen dat als ik een SQL-query met een WHERE-clause op `token` zou hebben, hij dus niet optimaal gebruik zou maken van de index, omdat de primary key samengesteld is.
Kan ik dan beter een aparte index zetten op token of is het gebruik van deze "surrogate" dienst_id perfect geschikt voor deze situatie?

Alvast dank...

[NMe]

Ik zou gaan voor de oplossing met een extra keyveld. Een key die afhankelijk is van andere, niet-key-velden is niet bepaald handig bij wijzigingen in je tabel. Primary keys moet je niet veranderen, en dat risico loop je dan wel namelijk.

Overigens is dit meer iets voor Software Engineering & Architecture. Zie ook Waar hoort mijn topic?

[EfBe]

Relational Model design 101: NOOIT maar dan ook NOOIT compromissen sluiten in je relationele model op basis van de database engine waarin het geimplementeerd wordt.

Zodra je dat doet, is de logical weg in je model en DUS kun je het weggooien. (voor de ciricasters: stel je gaat wisselen van database type, dan gelden bv die limieten niet meer, en dus zit je met een gehandicapt relationeel model wat niet gehandicapt hoeft te zijn, want het was goed voordat je het aanpaste aan de limieten van de db engine die je eerst gebruikten)

Over PK: Diensten (nooit tabelnamen als meervoud definieren, de tabel representeert de entity 'Dienst', niet de entity 'Diensten'. Er zijn er alleen meer dan 1 van (instances)), door WAT wordt dat uniek aangeduid? dienst_id ? Dan is dat de PK en gebruik je DAT attribute voor identificatie etc.

Surrogate keys zou ik vermijden, want ze geven eigenlijk aan dat je niet echt een keuze kon maken tussen verschillende uniek identificerende attributes.

Samengestelde PK's komen eigenlijk alleen voor in intermediate tables ('koppeltabellen'), immers je kunt bij ieder entity zeggen dat alle attributes tezamen de entity uniek identificeren, dus kies je voor een enkele uniek identificerende attribute, veelal een artificial key.

Overigens lijkt me dat Token ook uniek is, dus waarom heb je die niet als PK genomen?

En over die index: een normale database maakt gewoon een clustered index aan voor de PK, ookal is deze samengesteld. Je kunt dan ook gewoon een nieuwe index per attribute in die PK aanmaken, ter aanvulling. Ik heb geen idee of MySql dat ondersteunt, maar gezien het feit dat deze wonderlijke DB een default van NULL accepteert voor een PK field vrees ik het ergste.

[ Voor 9% gewijzigd door EfBe op 25-10-2006 11:06 ]

[DPLuS]

Tja, op zich zou dus de combinatie afdeling_id / token een record in deze tabel al uniek definiëren.
Maar uit het verhaal van NME begrijp ik dat het onverstandig is om deze als PK te gebruiken, daar token KAN veranderen.

Bovendien zou ik dan ook weer een aparte index moeten definiëren op token, mocht ik gebruik willen maken van een query waar 'WHERE token = ?' in voorkomt.

Dit wil ik even verduidelijken aan de hand van een voorbeeld.

Stel je voor je hebt de volgende koppel-tabel:

code:

+-------------+------------------+------+-----+---------+-------+
| Field       | Type             | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| Product_Id  | int(10) unsigned | NO   | PRI | 0       |       | 
| Software_Id | int(10) unsigned | NO   | PRI | 0       |       | 
+-------------+------------------+------+-----+---------+-------+

Zoals je ziet heb ik een samengestelde primary key.

Nu ga ik de volgende queries uitvoeren:

code:

mysql> EXPLAIN SELECT * FROM SoftwareProducts WHERE Product_Id = 87;
+----+-------------+------------------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table            | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+------------------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | SoftwareProducts | ref  | PRIMARY       | PRIMARY | 4       | const |    8 | Using index | 
+----+-------------+------------------+------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM SoftwareProducts WHERE Software_Id = 87;       
+----+-------------+------------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table            | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+------------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | SoftwareProducts | index | NULL          | PRIMARY | 8       | NULL |  746 | Using where; Using index | 
+----+-------------+------------------+-------+---------------+---------+---------+------+------+--------------------------+

Nu zie je bij de 2e query onder possible keys NULL staan.
Maar bij extra staat wel using index.
Wordt er nu wel of geen gebruik gemaakt van de index van die samengestelde PK?

[ Voor 71% gewijzigd door DPLuS op 25-10-2006 11:50 ]

[Verwijderd]

EfBe schreef op woensdag 25 oktober 2006 @ 11:05:
Over PK: Diensten (nooit tabelnamen als meervoud definieren, de tabel representeert de entity 'Dienst', niet de entity 'Diensten'. Er zijn er alleen meer dan 1 van (instances))

Daarover verschillen de geleerden van mening... De tabel representeert meerdere instances van 'Dienst', en zo'n collectie noem je dan dus 'Diensten'. Niks mis mee.

Surrogate keys zou ik vermijden, want ze geven eigenlijk aan dat je niet echt een keuze kon maken tussen verschillende uniek identificerende attributes.

Helemaal mee eens. Synthetische PK's introduceren wanneer je 't ook met 1 of meer natuurlijke velden uit de tabel kunt, is alleen maar overhead en maakt de boel er niet duidelijker of beheersbaarder op.

Ik heb geen idee of MySql dat ondersteunt, maar gezien het feit dat deze wonderlijke DB een default van NULL accepteert voor een PK field vrees ik het ergste.

*slik* Ik hou 't voorlopig maar even bij MSSQL en Interbase...