Posted on • Originally published atcleandatabase.wordpress.com on
#100CodeExamples – Only one active protocol
Sometimes you have the situation that you store several different variants of an entity in the database, but you have to be absolutely sure there is only one of these variants active at a time.
Examples could be different configurations or color schemes you can choose from, the active financial year in accounting-related scenarios or a default entry.
There’s a similar thing in the deathstar, which runs on one of several protocols.
ID | LABEL | ALERT_LEVEL | DEFENSE_MODE | POWER_LEVEL |
---|---|---|---|---|
1 | Everything easy | LOW | BE_KIND | 80 |
2 | Be careful | MEDIUM | BE_SUSPICIOUS | 90 |
3 | OMG the rebels! | VERY HIGH | SHOOT_FIRST_ASK_LATER | 120 |
To make sure, there is only ever one protocol active, the database developers use simple tools most relational databases provide:
/* We have several protocols for the deathstar but its important we only have one active protocol at a time */createtabledeathstar_protocols(idintegernotnullprimarykey,labelvarchar2(256),alert_levelvarchar2(16)notnull,defense_modevarchar2(32)notnull,power_levelnumber(5,2)notnull);insertintodeathstar_protocolsvalues(1,'Everything easy','LOW','BE_KIND',80);insertintodeathstar_protocolsvalues(2,'Be careful','MEDIUM','BE_SUSPICIOUS',90);insertintodeathstar_protocolsvalues(3,'OMG the rebels!','VERY HIGH','SHOOT_FIRST_ASK_LATER',120);select*fromdeathstar_protocols;/* To make sure there is only one possibly active protocol, we can use basic relational modeling in combination with constraints */createtabledeathstar_protocol_active(idintegernotnullprimarykey,only_onenumber(1)default1notnull,-- ID is also foreign keyconstraintdeathstar_prot_act_fkforeignkey(id)referencesdeathstar_protocols(id)ondeletecascade,-- Make sure there can only be one rowconstraintdeathstar_prot_act_uqunique(only_one),-- by limiting the possible value of the-- helper-columnconstraintdeathstar_prot_act_chkcheck(only_one=1));/* This also means the technique is usable in every relational database with check-constraints */insertintodeathstar_protocol_active(id)values(1);-- We cannot have more than one active protocolinsertintodeathstar_protocol_active(id)values(2);/* We can even have a view which shows the active protocol */createviewv_deathstar_protocolsasselectprot.id,label,alert_level,defense_mode,power_level,coalesce(active.only_one,0)is_activefromdeathstar_protocolsprotleftouterjoindeathstar_protocol_activeactiveonprot.id=active.id;select*fromv_deathstar_protocols;updatedeathstar_protocol_activesetid=2;select*fromv_deathstar_protocols;
You can run this example onLiveSQL, but it’s also possible on SQL Server and every other relational database with CHECK-constraints.
Top comments(0)
For further actions, you may consider blocking this person and/orreporting abuse