Progettazione di un database con riferimenti circolari per il caso one-of-many

Alla fine è una cosa facile. Il primo caso (il caso di riferimento circolare) interrompe la separazione del codice in un'applicazione. Perché l'immagine del profilo è una caratteristica della relazione tra utente e profilo. Non è una relazione separata e non dovrebbe essere modellata in quanto tale perché farlo è un duro lavoro. Se lo fai, ottieni l'unicità ma a prezzi folli. Il codice sta andando più strettamente accoppiato, forse più lento e leggermente duplicato in alcuni punti.

L'unicità della soluzione non circolare può essere gestita da un indice univoco parziale (utente, profilo = True). Questo indice rende anche le cose veloci. Infatti una semplice ricerca dell'immagine del profilo dell'utente da (user_id, True) sarà altrettanto veloce e forse anche più veloce (perché l'indice è più piccolo) rispetto alla ricerca di image.id.

Come argomento finale, immagina di voler recuperare tutte le immagini del profilo. Nel primo caso, devi necessariamente unirti agli utenti. Nel secondo caso, non è necessario.

Per un semplice progetto, probabilmente adotterei la seconda soluzione. D'altra parte, la prima soluzione potrebbe essere leggermente modificata per evitare gli inconvenienti menzionati. È possibile modellare un'immagine del profilo come caso speciale di un'immagine, ovvero l'ereditarietà in OOP o è una gerarchia nel DB relazionale.

Pertanto, tralasciando la colonna profile_image e introducendo la tabella

Profile image:
   image_id - foreign key to Image
   user_id - foreign key to User

con vincolo univoco su user_id e probabilmente una colonna chiave dedicata dedicata + indici su colonne utilizzate per il caso d'uso desiderato.

L'unico problema potrebbe essere l'ordinamento, in cui è necessario conservare alcuni pesi (una colonna in Image table ) in coerenza con la tabella Profile image .

Devi essere più specifico sul significato semantico delle tue entità. Non utilizzare user s o user_id s, perché la parola "utente" non esprime il tipo di utilizzo. Se hai in mente qualche tipo specifico di utilizzo, usa invece user . Ad esempio, se si tratta di una proprietà, chiamala proprietà.

Queste sono le entità con le loro chiavi primarie. Abbiamo immagini, persone e proprietà.

create table image (
  id integer,
  data text,
  primary key (id));

create table person (
  id integer,
  name text,
  profile_image integer,
  primary key (id));

create table ownership (
  image_id integer,
  person_id integer,
  primary key (image_id, person_id));

Queste sono le relazioni. Una proprietà è una n: m relazione tra immagini e persone. L'immagine del profilo è una relazione 1: n tra persone e proprietà.

alter table ownership
  add foreign key (image_id) references image (id);

alter table ownership
  add foreign key (person_id) references person (id);

alter table person
  add foreign key (id, profile_image) references ownership (person_id, image_id);

Aggiungi alcune immagini.

insert into image values (11, 'image 1');
insert into image values (12, 'image 2');
insert into image values (13, 'image 3');

E alcune persone.

insert into person values (21, 'alice');
insert into person values (22, 'bob');

E definisci, quale persona possiede quale immagine.

insert into ownership values (11, 21);
insert into ownership values (12, 22);

E ora Bob vuole impostare la sua immagine del profilo:

-- update person set profile_image = 11 where id = 22;
update person set profile_image = 12 where id = 22;
-- update person set profile_image = 13 where id = 22;

Solo il secondo aggiornamento funziona correttamente. Il primo e il terzo non riescono, a causa del vincolo di chiave esterna, che impone, che solo quelle immagini vengono utilizzate come immagine profilo, che sono di proprietà della persona specifica. Il primo aggiornamento fallisce, perché l'immagine 11 appartiene ad Alice e non a Bob. E nel terzo aggiornamento fallisce, perché l'immagine 13 non ha proprietario.