MySql auto increment på ukendt antal indexes

Hvis jeg skal lave et system for et udefineret antal brugere, hvor hver bruger får "sin egen tabel" med en auto increment kolonne, hvordan gør man så det uden at skulle oprette en tabel for hver bruger? jeg tænker noget i stil med:

Table InvoiceID

ID  |  InvoiceID | CostumerID
----------------------------------
1   |         1  |            1
2   |         2  |            1
3   |         1  |            2
4   |         2  |            2

Jeg ved ikke om MySQL har en feature som er velegnet til din opgave. Men ellers kan du vel forholdsvist nemt implementere det vha. transaktioner.

Lav en tabel hvor du gemmer enten det nyeste InvoiceID per CostumerID eller det næste InvoiceID per CostumerID. I denne tabel kan du bruge auto increment på CostumerID og oprette alle indgange med InvoiceID sat til 0 eller 1.

Når du så vil tilføje en faktura kører du en transaktion som først tæller InvoiceID op i tabellen med næste InvoiceID og dernæst opretter en indgang med det nye InvoiceID.

For at undgå korruption pga. fejl i transaktionen bør du samtidigt indføre et krav om at InvoiceID+CostumerID skal være unik. Det er der så vidt jeg husker en SQL feature som kan gennemtvinge.

I stedet for at bruge tabellen med InvoiceID og transaktioner kan du blot udnytte at kravet om unikt InvoiceID+CostumerID ellers vil resultere i fejl ved race conditions. Så vil koden uden brug af transaktioner først lave et opslag på det maksimale InvoiceID for det givne CostumerID. Dernæst lægges en til og værdien bruges som det nye InvoiceID. Hvis to samtidige opdateringer forsøger at oprette samme InvoiceID for et givent CostumerID vil den ene få fejl pga. betingelsen på unikt InvoiceID+CostumerID. I tilfælde af fejl skal koden blot prøve igen. Hvis ikke det udregnede InvoiceID vokser efter hvert forsøg må fejlen have en anden årsag, og så smides der en exception eller returneres en fejl på en anden måde som er passende for den valgte platform.

Jeg ved ikke hvilken af de to metoder der vil give den bedste performance. De er nok ca. lige nemme at implementere, men metoden med en separat tabel er nok mest forståelig.

Drop "InvoiceID" og brug ID som "InvoiceID". Så har du en tabel med transaktioner og en med kunder. Så danner du bare en relation i sand (My)SQL-ånd.

Daniel-Dane (3) skrev:

Drop "InvoiceID" og brug ID som "InvoiceID". Så har du en tabel med transaktioner og en med kunder. Så danner du bare en relation i sand (My)SQL-ånd.

Jeg tror ikke det var planen at bruge samme tabel til både kunder og fakturaer. Men måske er kunderne interesseret i at se deres fakturer nummereret fortløbende uden at der er spring hver gang en anden kunde udskriver en faktura.

På den anden side er der måske kunder, som faktisk gerne vil have at der er spring. Hvis fakturaer nummereres fortløbende fra 1 og opad kan deres kunder jo set præcist hvor mange fakturaer de udsteder. Nogen virksomheder ønsker ikke at omfanget af deres salg skal være så gennemsigtigt. Det er svært at finde på en bedre måde til at sløre det præcise omfang end ved at lade flere virksomheder deles om den pågældende tæller.

kasperd (4) skrev:

Men måske er kunderne interesseret i at se deres fakturer nummereret fortløbende uden at der er spring hver gang en anden kunde udskriver en faktura.

Det burde kunne gøres uden at skrive numrene i databasen.

Daniel-Dane (5) skrev:

Det burde kunne gøres uden at skrive numrene i databasen.

Det vil være tungt at beregne hver gang en faktura udskrives. Og hvis der nogensinde slettes fra databasen vil numrene pludseligt ændres.

#1

Inden du laver alt for meget så vil jeg foreslå dig at konsultere din revisor.

Det system du skal lave skal jo være lovligt i forhold til bogføringsloven.

Min opfattelse af bogføringslovens krav er at:
- faktura numre skal være fortløbne
- faktura numre skal være det som bruges som reel identifikation af faktura

Men jeg er *IKKE* revisor.

Hvis min opfattelse tilfældigvis skulle være rigtig, så bortfalder hele problemstillingen med "auto increment per customer".

#2

Transaction Isolation Level !

kasperd (2) skrev:

Lav en tabel hvor du gemmer enten det nyeste InvoiceID per CostumerID eller det næste InvoiceID per CostumerID. I denne tabel kan du bruge auto increment på CostumerID og oprette alle indgange med InvoiceID sat til 0 eller 1.

Når du så vil tilføje en faktura kører du en transaktion som først tæller InvoiceID op i tabellen med næste InvoiceID og dernæst opretter en indgang med det nye InvoiceID.

For at undgå korruption pga. fejl i transaktionen bør du samtidigt indføre et krav om at InvoiceID+CostumerID skal være unik. Det er der så vidt jeg husker en SQL feature som kan gennemtvinge.

Hvis transation isolation level er repeatable read eller højere, så burde det ikke være nødvendigt med det unikke indeks.

kasperd (2) skrev:

I stedet for at bruge tabellen med InvoiceID og transaktioner kan du blot udnytte at kravet om unikt InvoiceID+CostumerID ellers vil resultere i fejl ved race conditions. Så vil koden uden brug af transaktioner først lave et opslag på det maksimale InvoiceID for det givne CostumerID. Dernæst lægges en til og værdien bruges som det nye InvoiceID. Hvis to samtidige opdateringer forsøger at oprette samme InvoiceID for et givent CostumerID vil den ene få fejl pga. betingelsen på unikt InvoiceID+CostumerID. I tilfælde af fejl skal koden blot prøve igen.

Hvis transation isolation level er serializable, så burde det ikke være nødvendigt med det unikke indeks.

kasperd (2) skrev:

Jeg ved ikke hvilken af de to metoder der vil give den bedste performance.

Sandsynligvis ligegyldigt.

Hvis det ene tager 20 ms og det andet tager 40 ms på en tilfældig PC, så er det 3000 og 1500 fakturaer i minuttet. Hvis det er for lidt har man råd til et bedre disk system.

#rackbox

Hvis du vælger transaktioner så husk:
1) det skal være InnoDB tabeller ikke myISAM tabeller
2) du skal retry in case of lock timeout
3) transactions, auto increment og krav om fortløbne numre duer ikke (auto increment skipper numret i tilfælde af rollback)

arne_v (8) skrev:

Hvis transation isolation level er repeatable read eller højere, så burde det ikke være nødvendigt med det unikke indeks.

Formålet var at fange fejl i logikken på højere niveau. Det kan transaktionerne ikke hjælpe med.

Jeg har muligvis ikke forklaret mig godt nok.. Eksemplet var generisk, og egentlig blot for at vise, hvad jeg ville opnå.. Når jeg laver en

"insert into Invoice values ('', '',1)"

så skal den selv sætte de to første auto increment felter automatisk. Det første felt er en unik ID for "relationen" kunde<->fakura og det andet er et unikt ID for fakturaen for den pågældende kunde...

I denne sammenhæng er en "kunde" altså min kunde... Et fakturasystem med flere brugere/kunder. Hvis virksomhed A begynder at fakturere, så skal deres numre naturligvis være fortløbende, selvom virksomhed B også fakturerer indimellem. Og der er et ukendt antal virksomheder, der skal kunne benytte systemet.

I øvrigt fortæller lovgivningen, at fakturanumre ikke nødvendigvis behøver at være fortløbende, eller kun bestå af tal, men de skal være unikke for fakturaen og tallet skal stige. Dette for ikke at have en faktura med et lavere nummer, selvom datoen er senere.

Gav det bedre mening? ID skal incrementeres fra 1 og frem. Fakturanummer skal ligeså ikcrementeres fra 1 og frem for HVER bruger. Der kan altså være flere faktura nr. 1, men KUN 1 pr. bruger.

Det må da kunne lade sig gøre...

#12

Så customer (stavet costumer i dit feltnavn) er flere forskellige leverandører som alle laver deres fakturering i det (multi-tenant) system du er ved at lave.

De beskrevne metoder virker fint til at det du vil.

#12

Du skal dog så have sikret dig at modellen med flere virksomheders data i samme tabel er lovlig i forhold til bogføringsloven og persondataloven.

rackbox (12) skrev:

I øvrigt fortæller lovgivningen, at fakturanumre ikke nødvendigvis behøver at være fortløbende, eller kun bestå af tal, men de skal være unikke for fakturaen og tallet skal stige. Dette for ikke at have en faktura med et lavere nummer, selvom datoen er senere.

http://www.skat.dk/SKAT.aspx?thisId=108095.205214

siger:

Et fortløbende nummer, der bygger på én eller flere serier, og som identificerer fakturaen (fakturanummer)

kasperd (11) skrev:

Formålet var at fange fejl i logikken på højere niveau. Det kan transaktionerne ikke hjælpe med.

Det giver god mening at have restriktionen i databasen for at fange fejl i applikationen.

Men restriktionen er nødvendig ved read committed eller lavere selv med korrekt kode.