Come si generano i riferimenti alle chiavi esterne nella tabella?

TLDR;

Secondo la mia esperienza, se si hanno relazioni tra tabelle, dovrebbero essere le chiavi esterne appropriate, applicate con i vincoli. Se scegli un riferimento debole come quello che descrivi finirai per finire nei guai.

Puoi farlo usando l'ereditarietà nel tuo modello a oggetti e rappresentarlo nel tuo database con chiavi esterne 1-a-1 dalla tabella 'superclasse' alle tabelle 'sottoclasse' e una chiave esterna dal tuo 'Mi piace' 'tabella alla tabella' superclasse '. Continua a leggere per una spiegazione più completa.

La storia completa Il modo in cui Entity Framework fa ciò sarebbe utilizzare l'ereditarietà nel modello oggetto (es. Post e sottoclassi Immagine, Commento, Video). Nei database ci sarebbe quindi una tabella chiamata Post, con tutti i campi comuni a tutti i tipi, e le tabelle Image, Comment, Video per i campi specifici per ogni tipo. La chiave primaria di ogni sottoclasse di tabelle è anche la chiave esterna della chiave primaria di Post (quindi esiste un mapping 1-a-1 tra Post.Id e Image.Id ecc., Sono lo stesso id). La tua tabella Mi piace quindi le chiavi esterne da pubblicare.

Psuedo-SQL di seguito:

table Post (
    Id int not null PK,
    UserId int not null REFERENCES User.Id
)

table Image (
    Id int not null PK REFERENCES Post.Id,
    ImageUrl varchar(250) null
)

table Likes (
    Id int not null PK,
    UserId int not null REFERENCES User.Id,
    PostId int not null REFERENCES Post.Id
)

Ho anche trovato un esempio in cui c'è una tabella chiamata qualcosa come "LikeableType" che ha un Id e il nome della tabella che digita i riferimenti alle domande (quindi si dovrebbe avere una riga per ciascuna di Immagine, Commento e video). La tabella "Mi piace" presenta quindi un campo "LikeableType" e un campo "LikeableId". Il tuo processo di ricerca sarebbe quindi qualcosa di simile a:

• LiekableType = 1 quindi table = 'Image'
• Seleziona dalla tabella in cui Id = LikeableId

Il problema qui è duplice: in primo luogo, non è possibile imporlo con un vincolo di chiave esterna, poiché si tratta di una meta-relazione, piuttosto che di una vera relazione a livello di database. In secondo luogo - e questo si è verificato nell'esempio che ho visto - è che i tipi di chiavi potrebbero essere diversi per ogni tabella a cui vuoi fare riferimento (cioè l'immagine ha una chiave int, ma il video ha una chiave varchar), quindi il casting per varchar ecc è necessario per poter gestire le chiavi in un formato comune.

Un approccio generico che ho usato una volta:

table Likable (
  id int not null primary key, -- the common ID
  user_id int not null, -- the creator of the likable asset
  created datetime,
  updated datetime,
  ... -- etc
);

table Post (
  id int not null primary key,
  title varchar,
  text varchar,
  ... -- etc
  foreign key (id) references Likable(id)
);

-- a Picture can only be attached to a Post
table Picture (
  id int not null primary key,
  title varchar,
  url varchar,
  width int,
  height int,
  post_id int not null,
  ... -- etc
  foreign key (id) references Likable(id),
  foreign key (post_id) references Post(id),
);

-- a Like can be added to anything Likable
table "Like" (
  id int not null primary key,
  target_id int not null, -- the asset 
  user_id int not null, -- the user that liked it
  foreign key (target_id) references Likable(id)
);

Ciò ti consente di assicurarti che qualsiasi cosa a cui aggiungi un "mi piace" sia un'immagine o un post, ma non altro.

Penso che sia possibile spiegare questa configurazione a SQLAlchemy; non sono sicuro di Django ORM.

Lati negativi:

• devi inserire le cose due volte e assicurati di non creare accidentalmente un Mikable senza un link o un'immagine.
• Devi viaggiare fino a Likable per i campi comuni; può costarti una richiesta in più se non stai attento.

Upsides:

• Integrità referenziale su tutta la linea:)
• Spazio ID comune per i Mi piace, ed è facile aggiungere altri tipi (Video, Musica, ecc.).
• Gestione semplice e coerente dei campi comuni in Likable (nel mio caso ne avevo molti).

Vedo tre diverse soluzioni dal punto di vista del design del DB. Non sono sicuro di come il tuo layer ORM (entity framework) apparirà basato su questi progetti, ma mi piace avvicinarmi a queste cose da un progetto di DB.

Opzione 1

Quella che hai menzionato, ma per applicare l'integrità referenziale devi aggiungere un'altra colonna:

CREATE TABLE 'likes' (
'target' int(11),
'targetTypeID' int(11),
'user_id' int(11),
'created_at' timestamp
)

Quindi avresti bisogno di una tabella targetType, che contiene 3 voci; Video, immagine e commento. Sulla base di questa tabella, è possibile scrivere un trigger per applicare l'integrità referenziale: si controllerà il valore targetTypeID e quindi si verificherà il valore target corrispondente. . Un po 'incasinato, ma funzionerebbe. Se vuoi semplificare la procedura, puoi aggiungere TargetTypeID come colonna alla tabella Video, Immagine e Commento, quindi puoi eseguire una query in questo modo:

Select  l.*
from    Likes l
left outer join image i on l.target = i.imageid and l.targettypeid = i.targettypeid
left outer join video v on l.target = v.videoid and l.targettypeid = i.targettypeid
left outer join comment c on l.target = c.commentid and l.targettypeid = i.targettypeid

che ti aiuterà ad evitare di entrare accidentalmente in una fila di "Video Mi piace" nella tabella Mi piace alla tabella sbagliata, ad esempio come una riga "Immagine". E sì, se è possibile, succederà sicuramente .

Opzione due

Crea la tua tabella in questo modo:

CREATE TABLE 'likes' (
LikeID int primary key,
ImageID int null,
VideoID int null,
CommentID int null,
'user_id' int(11),
'created_at' timestamp
)   

È quindi possibile inserire un vincolo (o un trigger) che indica che "IDImmagine" o "IDVideo" o "IDDIP" devono essere compilati. Il vantaggio principale di questo design è che è possibile applicare integralmente l'integrità referenziale utilizzando il DB. Il lato negativo è che stai "sprecando" due colonne che saranno sempre nulle. L'altro lato negativo è che le tue query saranno simili a questa:

Select  l.*
from    Likes l
left outer join image i on l.imageid = i.imageid
left outer join video v on l.videoid = v.videoid
left outer join comment c on l.commentid = c.commentid

che è alquanto fastidioso. Ma forse non avrai bisogno di fare quel genere di cose molto spesso, specialmente se metti le cose comuni (es. Nome, descrizione) nella tabella dei Mi piace.

Opzione tre

Uso di "ereditarietà" o una tabella di base. Crea una nuova tabella chiamata "ItemLike":

create table ItemLike (
    ItemLikeID int,
    TargetTypeID int,
    ImageID int null,
    VideoID int null,
    CommentID int null,
)

e cambia la tua tabella simile in questo modo:

CREATE TABLE 'likes' (
    LikeID int primary key,
    ItemLikeID int foreign key,
    'user_id' int(11),
    'created_at' timestamp
)   

molto simile alla seconda risposta ma semplicemente aggiungendo un altro livello di astrazione e potrebbe rendere più belle le classi del tuo Entity Framework. Un po 'inutile dal punto di vista del progetto DB, a meno che tu non voglia rendere ItemLikeID nullable (non riesco a capire il motivo per cui lo faresti), ma potrebbe rendere più facile il partizionamento della tabella se ottieni tanti e tanti dati nella tua tabella simile. I tuoi join quindi assomigliano a questo:

Select  l.*
from    Likes l
inner join ItemLike il on l.ItemLikeID = il.ItemLikeID
left outer join image i on il.imageid = i.imageid
left outer join video v on il.videoid = v.videoid
left outer join comment c on il.commentid = c.commentid

Oppure potresti usare un database NoSQL e tutti i tuoi problemi andranno via! < / sarcasmo >

Questo mi sembra un SuperType - SubType relationship dovrebbe essere usato. Il SuperType è una tabella singola che memorizza tutti gli attributi comuni di tutti i sottotipi insieme alla chiave che indica che tipo di sottotipo è. Ad esempio:

tblLikes (SuperType)
LikedId, (PKey)
LikedType, (1 = Immagini, 2 = Video 3 = Commenti)
LikeShortDescription,
UserId,
DateStamp

tblLikedImages (Sottotipo)
ImageId, (PKey)
LikedId, (FKey tblLikes)
...
ImageData va qui
...

tblLikedVideo (Sottotipo)
VideoId, (PKey)
LikedId, (FKey tblLikes)
...
VideoData va qui
...

tblLikedComments (Sottotipo)
CommentId, (PKey)
LikedId, (FKey tblLikes)
...
CommentData Va qui
...

Il concetto consente la scalabilità. Ogni sottotipo può o non può appartenere al SuperType. Una query della tabella SuperType può fornire una vista di alto livello di tutti gli "Articoli preferiti". Sfortunatamente se hai bisogno dei dettagli di ogni sottotipo ... devono essere eseguite delle query separate.