[Oracle DB] "Sub" Sequences

[Verwijderd]

Ik ben bezig met het opzetten van de database in Oracle.
Ik wil namelijk een multi-organization database opzetten
waarbij elke tabel informatie bevat voor alle organizations.

De ORGANIZATIONS tabel heeft dan een
organization_id
organization_name

De primary key is hier de organization_id.

Er is bijvoorbeeld een PERSONS tabel met de werknemers voor elke organization. (Er zijn nog veel meer tabellen waarbij de opzet soortgelijk is)

deze tabel heeft dan de volgende velden:
organization_id
person_id
person_name

De primary key is hier een composite key die bestaat uit de organization_id en de person_id.

Is het mogelijk om voor elke organization de person_ids oplopend te nummeren zoals:

ORGANIZATIONS
organization_id organization_name
01 Corporation One
02 Corporation Two

PERSONS
organization_id person_id person_name
01 01 John
01 02 Jimmy
01 03 Jacky
02 01 James
02 02 Johnny
02 03 Jack
01 04 Jane

Bestaat er hier een efficiente oplossing voor?
Ik weet wel dat je in Oracle een SEQUENCE moet definieren
die je dan met een INSERT BEFORE trigger toewijst aan de betreffende ID kolom:
CREATE OR REPLACE TRIGGER PERSONS_TRG
BEFORE INSERT ON PERSONS
FOR EACH ROW BEGIN
SELECT PERSONS_SEQ.nextval INTO :new.PERSON_ID FROM dual;
END;

op deze manier zou je dus krijgen:

organization_id person_id person_name
01 01 John
01 02 Jimmy
01 03 Jacky
02 04 James
02 05 Johnny
02 06 Jack
01 07 Jane

Is het mogelijk om de person_id's voor elke organization oplopend te laten zijn?
(Ik weet ook dat je met een sequence ook gaps kan krijgen als de server
crasht, maar dat even terzijde)

De oplossing waar ik zelf aan dacht is door het gebruik van een functie zoals:

CREATE FUNCTION MAX_ID(id IN NUMBER) AS
DECLARE
Next_No Number(10);
BEGIN
select NVL(MAX(PERSON_ID),0) + 1 into Next_No from PERSONS
where ORGANIZATION_ID=id;
RETURN Next_No;
END;

CREATE OR REPLACE TRIGGER PERSONS_TRG
BEFORE INSERT ON PERSONS
FOR EACH ROW BEGIN
select MAX_ID(:new.ORGANIZATION_ID) into :new.PERSON_ID from dual;
END;

Mijn bedenkingen bij deze oplossingen zijn dat er performance problemen op kunnen treden, en misschien zelfs dubbele keys als er veel requests plaatsvinden (ik heb me nog niet verdiept in het Oracle locking systeem).

Wie heeft er enig idee of dit efficient te doen is?

[EfBe]

sequences zonder 'gaps' zijn niet mogelijk. Je krijgt nl. gaten wanneer een insert failed in een transaction die faalt. Ook kun je een sequence niet meerdere values laten returnen, zoals jij wilt. Wat jij wilt doen met select max e.d. is niet thread safe en vragen om moeilijkheden.

Waarom is het zo belangrijk dat die nummers aansluitend oplopend zijn per organisatie? Want, wat te doen wanneer een personeelslid weggaat, dan delete je de row, en moet je die ID opnieuw gebruiken...

[JaQ]

Kan je ook maar 1 goede reden bedenken waarom je uberhaupt die sequence nummers aansluitend binnen 1 organisatie wilt hebben? Een id is niet voor niets een betekenisloze sleutel.

Het is misschien leuk voor je om eens naar de VPN functies van Oracle te kijken. Daar kan je bijzonder leuke resultaten mee halen voor een setup als deze.