Właściwą strukturą dla tego scenariusza jest model SubClass / Dziedziczenie i jest on prawie identyczny z koncepcją zaproponowaną w tej odpowiedzi: Heterogeniczna uporządkowana lista wartości .
Model zaproponowany w tym pytaniu jest właściwie dość podobny , ponieważ Animaljednostka zawiera typ (tj. race) I właściwości wspólne dla wszystkich typów. Konieczne są jednak dwie niewielkie zmiany:
Usuń pola Cat_ID i Dog_ID z odpowiednich encji:
Kluczowym założeniem jest to, że wszystko to Animal, bez względu na race: Cat, Dog, Elephant, i tak dalej. Biorąc pod uwagę, że punkt wyjścia, każde szczególności racez Animalnie naprawdę potrzebny jest oddzielny identyfikator, ponieważ:
Animal_IDjest wyjątkowy- te
Cat, Dogoraz wszelkie inne racepodmioty, dodane w przyszłości nie przez siebie, w pełni reprezentować jakiś konkretny Animal; mają jedynie znaczenie, gdy używany w połączeniu z informacjami zawartymi w jednostce dominującej Animal.
Stąd Animal_IDnieruchomość w Cat, Dogitp podmiotów jest zarówno PK i FK powrotem do Animaljednostki.
Rozróżnij rodzaje breed:
To, że dwie właściwości mają tę samą nazwę, niekoniecznie oznacza, że są one takie same, nawet jeśli ta sama nazwa implikuje taki związek. W tym przypadku, co naprawdę trzeba to faktycznie CatBreedi DogBreedjako oddzielne „typów”
Uwagi wstępne
- SQL jest specyficzny dla Microsoft SQL Server (tzn. Jest T-SQL). Oznacza to, że należy uważać na typy danych, ponieważ nie są one takie same we wszystkich RDBMS. Na przykład używam,
VARCHARale jeśli chcesz przechowywać coś poza standardowym zestawem ASCII, powinieneś naprawdę użyć NVARCHAR.
- Pola ID tabel „typów” (
Race, CatBreedi DogBreed) nie są automatycznie zwiększane (tj. TOŻSAMOŚĆ w kategoriach T-SQL), ponieważ są stałymi aplikacji (tj. Są częścią aplikacji), które są statycznymi wartościami wyszukiwania w bazy danych i są reprezentowane jako enums w C # (lub innych językach). Jeśli wartości są dodawane, są one dodawane w kontrolowanych sytuacjach. Zastrzegam stosowanie pól automatycznego przyrostu dla danych użytkownika, które przychodzą za pośrednictwem aplikacji.
- Używam konwencji nazewnictwa, aby nazwać każdą tabelę podklasy, zaczynając od nazwy głównej klasy, po której następuje nazwa podklasy. Pomaga to uporządkować tabele, a także wyraźnie wskazuje (bez patrzenia na FK) związek tabeli podklasy z tabelą głównej encji.
- Zapoznaj się z sekcją „Edycja końcowa” na końcu, aby uzyskać informacje dotyczące wyświetleń.
„Rasa” jako podejście „wyścigowe”
Ten pierwszy zestaw tabel to tabele wyszukiwania / typów:
CREATE TABLE Race
(
RaceID INT NOT NULL PRIMARY KEY
RaceName VARCHAR(50) NOT NULL
);
CREATE TABLE CatBreed
(
CatBreedID INT NOT NULL PRIMARY KEY,
BreedName VARCHAR(50),
CatBreedAttribute1 INT,
CatBreedAttribute2 VARCHAR(10)
-- other "CatBreed"-specific properties as needed
);
CREATE TABLE DogBreed
(
DogBreedID INT NOT NULL PRIMARY KEY,
BreedName VARCHAR(50),
DogBreedAttribute1 TINYINT
-- other "DogBreed"-specific properties as needed
);
Ta druga lista to główna jednostka „Zwierząt”:
CREATE TABLE Animal
(
AnimalID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
RaceID INT NOT NULL, -- FK to Race
Name VARCHAR(50)
-- other "Animal" properties that are shared across "Race" types
);
ALTER TABLE Animal
ADD CONSTRAINT [FK_Animal_Race]
FOREIGN KEY (RaceID)
REFERENCES Race (RaceID);
Trzeci zestaw tabel to uzupełniające się podklasy, które uzupełniają definicję każdego Racez Animal:
CREATE TABLE AnimalCat
(
AnimalID INT NOT NULL PRIMARY KEY, -- FK to Animal
CatBreedID INT NOT NULL, -- FK to CatBreed
HairColor VARCHAR(50) NOT NULL
-- other "Cat"-specific properties as needed
);
ALTER TABLE AnimalCat
ADD CONSTRAINT [FK_AnimalCat_CatBreed]
FOREIGN KEY (CatBreedID)
REFERENCES CatBreed (CatBreedID);
ALTER TABLE AnimalCat
ADD CONSTRAINT [FK_AnimalCat_Animal]
FOREIGN KEY (AnimalID)
REFERENCES Animal (AnimalID);
CREATE TABLE AnimalDog
(
AnimalID INT NOT NULL PRIMARY KEY, -- FK to Animal
DogBreedID INT NOT NULL, -- FK to DogBreed
HairColor VARCHAR(50) NOT NULL
-- other "Dog"-specific properties as needed
);
ALTER TABLE AnimalDog
ADD CONSTRAINT [FK_AnimalDog_DogBreed]
FOREIGN KEY (DogBreedID)
REFERENCES DogBreed (DogBreedID);
ALTER TABLE AnimalDog
ADD CONSTRAINT [FK_AnimalDog_Animal]
FOREIGN KEY (AnimalID)
REFERENCES Animal (AnimalID);
Model wykorzystujący breedtyp współdzielony pokazano po sekcji „Uwagi dodatkowe”.
Dodatkowe uwagi
- Koncepcja
breedwydaje się być centralnym punktem zamieszania. Jcolebrand zasugerował (w komentarzu do pytania), że breedjest to właściwość wspólna dla różnych races, a pozostałe dwie odpowiedzi mają ją jako taką w swoich modelach. Jest to jednak błąd, ponieważ wartości dla breednie są współużytkowane przez różne wartości race. Tak, jestem świadomy, że dwa inne proponowane modele próbują rozwiązać ten problem, tworząc racerodzic breed. Chociaż to technicznie rozwiązuje problem relacji, nie pomaga w rozwiązaniu ogólnego pytania dotyczącego modelowania tego, co zrobić z nietypowymi właściwościami, ani jak poradzić sobie z racetym, co nie ma breed. Ale w przypadku, gdy zagwarantowano, że taka własność istniałaby we wszystkichAnimals, do tego dołączę opcję (poniżej).
- Modele zaproponowane przez vijayp i DavidN (które wydają się identyczne) nie działają, ponieważ:
- Oni też
- nie zezwalaj na przechowywanie nietypowych właściwości (przynajmniej nie dla poszczególnych instancji
Animal), lub
- wymagają, aby wszystkie właściwości wszystkich
races były przechowywane w Animalencji, która jest bardzo płaskim (i prawie nierelacyjnym) sposobem reprezentowania tych danych. Tak, ludzie robią to przez cały czas, ale oznacza to, że dla wielu właściwości, które nie są przeznaczone dla tego konkretnego, jest wiele pól NULL na wiersz raceORAZ wiedza, które pola na wiersz są powiązane z danym racerekordem.
- Oni nie pozwalają na dodanie
racew Animalprzyszłości, które nie mają breedjako własność. A nawet jeśli wszystko Animals mają breed, że nie zmieni strukturę ze względu na to, co zostało wcześniej zauważono około breed: To breedzależy od race(czyli breeddla Catnie to samo, co breeddla Dog).
„Rasa” jako podejście do własności wspólnej / wspólnej
Proszę zanotować:
Poniższy kod SQL można uruchomić w tej samej bazie danych, co model przedstawiony powyżej:
RaceTabeli jest taka samaBreedTabela jest nowy- Do trzech
Animaltabel dołączono znak „2
- Nawet jeśli
Breedjest to obecnie wspólna własność, nie wydaje się słuszne, aby nie Raceodnotować tego w głównej / macierzystej jednostce (nawet jeśli jest to technicznie poprawne). Oba RaceIDi BreedIDsą reprezentowane w Animal2. Aby zapobiec niedopasowaniu między RaceIDzanotowanym Animal2i a BreedIDinnym RaceID, dodałem FK na obu, RaceID, BreedIDktóry odwołuje się do UNIKALNEGO OGRANICZENIA tych pól w Breedtabeli. Zwykle nie znoszę wskazywać FK na WYJĄTKOWE OGRANICZENIE, ale oto jeden z niewielu ważnych powodów, aby to zrobić. WYJĄTKOWY OGRANICZENIE jest logicznie „kluczem alternatywnym”, co czyni go ważnym dla tego zastosowania. Należy również pamiętać, że Breedtabela wciąż ma PK tylko BreedID.
- Powodem, dla którego nie było tylko PK na połączonych polach i brak WYJĄTKOWEGO OGRANICZENIA jest to, że pozwoliłoby to
BreedIDna powtórzenie tego samego dla różnych wartości RaceID.
- Powodem, dla którego PK i UNIKALNE OGRANICZENIE nie są przełączane, jest to, że może to nie być jedyne użycie
BreedID, więc nadal powinno być możliwe odniesienie się do konkretnej wartości Breedbez jej RaceIDdostępności.
- Chociaż poniższy model działa, ma dwie potencjalne wady dotyczące koncepcji współdzielenia
Breed(i dlatego wolę tabele Race-specyficzne Breed).
- Istnieje domniemane założenie, że WSZYSTKIE wartości
Breedmają te same właściwości. W tym modelu nie ma łatwego sposobu na uzyskanie rozbieżnych właściwości między Dog„rasami” i Elephant„rasami”. Istnieje jednak sposób, aby to zrobić, co zostało odnotowane w sekcji „Edycja końcowa”.
- Nie ma sposobu, aby podzielić
Breedsię więcej niż jedną rasą. Nie jestem pewien, czy jest to pożądane (a może nie w koncepcji zwierząt, ale być może w innych sytuacjach, w których używałby tego typu modelu), ale nie jest to możliwe tutaj.
CREATE TABLE Race
(
RaceID INT NOT NULL PRIMARY KEY,
RaceName VARCHAR(50) NOT NULL
);
CREATE TABLE Breed
(
BreedID INT NOT NULL PRIMARY KEY,
RaceID INT NOT NULL, -- FK to Race
BreedName VARCHAR(50)
);
ALTER TABLE Breed
ADD CONSTRAINT [UQ_Breed]
UNIQUE (RaceID, BreedID);
ALTER TABLE Breed
ADD CONSTRAINT [FK_Breed_Race]
FOREIGN KEY (RaceID)
REFERENCES Race (RaceID);
CREATE TABLE Animal2
(
AnimalID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
RaceID INT NOT NULL, -- FK to Race, FK to Breed
BreedID INT NOT NULL, -- FK to Breed
Name VARCHAR(50)
-- other properties common to all "Animal" types
);
ALTER TABLE Animal2
ADD CONSTRAINT [FK_Animal2_Race]
FOREIGN KEY (RaceID)
REFERENCES Race (RaceID);
-- This FK points to the UNIQUE CONSTRAINT on Breed, _not_ to the PK!
ALTER TABLE Animal2
ADD CONSTRAINT [FK_Animal2_Breed]
FOREIGN KEY (RaceID, BreedID)
REFERENCES Breed (RaceID, BreedID);
CREATE TABLE AnimalCat2
(
AnimalID INT NOT NULL PRIMARY KEY, -- FK to Animal
HairColor VARCHAR(50) NOT NULL
);
ALTER TABLE AnimalCat2
ADD CONSTRAINT [FK_AnimalCat2_Animal2]
FOREIGN KEY (AnimalID)
REFERENCES Animal2 (AnimalID);
CREATE TABLE AnimalDog2
(
AnimalID INT NOT NULL PRIMARY KEY,
HairColor VARCHAR(50) NOT NULL
);
ALTER TABLE AnimalDog2
ADD CONSTRAINT [FK_AnimalDog2_Animal2]
FOREIGN KEY (AnimalID)
REFERENCES Animal2 (AnimalID);
Ostateczna edycja (mam nadzieję ;-)
- Jeśli chodzi o możliwość (i potem) w trudność obsługi odmienne właściwości pomiędzy typów
Breed, to jest możliwe stosowanie tego samego podklasy / spadku koncepcji, ale w Breedpostaci głównej jednostki. W tym ustawieniu Breedtabela miałaby właściwości wspólne dla wszystkich typów Breed(podobnie jak Animaltabela) i RaceIDreprezentowałaby typ Breed(taki sam jak w Animaltabeli). Wtedy trzeba podklasy, takie jak tabele BreedCat, BreedDogi tak dalej. W przypadku mniejszych projektów można to uznać za „nadmierną inżynierię”, ale wspomniano o niej jako o opcjach w sytuacjach, które z niej skorzystałyby.
W przypadku obu podejść czasami pomaga tworzenie widoków jako skrót do pełnych elementów. Rozważ na przykład:
CREATE VIEW Cats AS
SELECT an.AnimalID,
an.RaceID,
an.Name,
-- other "Animal" properties that are shared across "Race" types
cat.CatBreedID,
cat.HairColor
-- other "Cat"-specific properties as needed
FROM Animal an
INNER JOIN AnimalCat cat
ON cat.AnimalID = an.AnimalID
-- maybe add in JOIN(s) and field(s) for "Race" and/or "Breed"
- Chociaż nie są one częścią jednostek logicznych, dość często w tabelach znajdują się pola kontroli, aby przynajmniej zorientować się, kiedy rekordy są wstawiane i aktualizowane. W praktyce:
CreatedDatePola zostanie dodana do Animaltabeli. To pole nie jest potrzebne w żadnej z tabel podklasy (np. AnimalCat), Ponieważ wiersze wstawiane dla obu tabel powinny być wykonywane w tym samym czasie w ramach transakcji.LastModifiedDatePola zostanie dodana do Animaltabeli i wszystkich stołach podklasy. To pole jest aktualizowane tylko wtedy, gdy ta konkretna tabela jest aktualizowana: jeśli aktualizacja wystąpi w określonym, AnimalCatale nie Animaldla określonego AnimalID, to ustawione zostanie tylko LastModifiedDatepole w AnimalCat.
