Zgodnie z opisem rozpatrywanego środowiska biznesowego istnieje struktura podtypu , która obejmuje Przedmiot - nadtyp - i każdą z jego kategorii , tj. Samochód , Łódź i Samolot (wraz z dwoma innymi, które nie zostały ujawnione) - podtypy—.
Poniżej opiszę metodę, którą zastosowałbym do zarządzania takim scenariuszem.
Zasady biznesowe
Aby rozpocząć wyznaczanie odpowiedniego schematu pojęciowego , niektóre z najważniejszych dotychczas ustalonych reguł biznesowych (ograniczając analizę tylko do trzech ujawnionych kategorii , aby zachować możliwie najkrótszą formę) można sformułować w następujący sposób:
- Użytkownik posiada zero-jeden-lub-wiele przedmioty
- Przedmiot jest własnością dokładnie-jednego użytkownika w określonym momencie
- Pozycja może być w posiadaniu jeden do wielu użytkowników w różnych punktach czasowych
- Przedmiot jest klasyfikowany przez dokładnie jednej kategorii
- Przedmiot jest, w każdym czasie,
- albo samochód
- lub łódź
- lub samolot
Ilustracyjny schemat IDEF1X
Rysunek 1 przedstawia schemat IDEF1X 1, który utworzyłem, aby pogrupować poprzednie formuły wraz z innymi regułami biznesowymi, które wydają się istotne:
Nadtyp
Z jednej strony, element , nadtyp, przedstawia właściwości † lub atrybuty wspólne dla wszystkich kategorii , tj.
- CategoryCode - określony jako KLUCZ ZAGRANICZNY (FK), który odwołuje się do Category.CategoryCode i działa jako dyskryminator podtypu , tj. Wskazuje dokładną kategorię podtypu, z którym dany Element musi być połączony -,
- OwnerId - wyróżniony jako FK, który wskazuje User.UserId , ale przypisałem mu nazwę roli 2 , aby dokładniej odzwierciedlić jej specjalne implikacje—,
- foo ,
- bar ,
- Baz i
- CreatedDateTime .
Podtypy
Z drugiej strony właściwości ‡ odnoszą się do każdej konkretnej kategorii , tj.
- Qux i Corge ;
- Grault , Garply and Plugh ;
- Xyzzy , Thud , Wibble and Flob ;
są pokazane w odpowiednim polu podtypu.
Identyfikatory
Następnie Item.ItemId PRIMARY KEY (PK) migrował 3 do podtypów o różnych nazwach ról, tj.
- CarId ,
- BoatId i
- PlaneId .
Stowarzyszenia wzajemnie się wykluczające
Jak pokazano, istnieje związek lub relacja liczności jeden do jednego (1: 1) pomiędzy (a) każdym wystąpieniem nadtypu i (b) jego komplementarną instancją podtypu.
Wyłącznym podtyp symbol obrazuje fakt, że podtypy wykluczają się wzajemnie, czyli konkretna pozycja występowanie może być uzupełniona tylko jednej instancji podtyp: albo jeden samochód , lub jeden samolot , albo jeden Łódź (nigdy przez dwóch lub więcej).
† , ‡ Użyłem klasycznych nazw symboli zastępczych, aby upoważnić niektóre właściwości typu jednostki, ponieważ ich rzeczywiste nazwy nie zostały podane w pytaniu.
Układ logiczny na poziomie ekspozycyjnym
W związku z tym, w celu omówienia logicznego projektu ekspozytora, wyprowadziłem następujące instrukcje SQL-DDL na podstawie diagramu IDEF1X wyświetlonego i opisanego powyżej:
-- You should determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on your business context characteristics.
-- Also, you should make accurate tests to define the
-- most convenient INDEX strategies based on the exact
-- data manipulation tendencies of your business context.
-- As one would expect, you are free to utilize
-- your preferred (or required) naming conventions.
CREATE TABLE UserProfile (
UserId INT NOT NULL,
FirstName CHAR(30) NOT NULL,
LastName CHAR(30) NOT NULL,
BirthDate DATE NOT NULL,
GenderCode CHAR(3) NOT NULL,
Username CHAR(20) NOT NULL,
CreatedDateTime DATETIME NOT NULL,
--
CONSTRAINT UserProfile_PK PRIMARY KEY (UserId),
CONSTRAINT UserProfile_AK1 UNIQUE ( -- Composite ALTERNATE KEY.
FirstName,
LastName,
GenderCode,
BirthDate
),
CONSTRAINT UserProfile_AK2 UNIQUE (Username) -- ALTERNATE KEY.
);
CREATE TABLE Category (
CategoryCode CHAR(1) NOT NULL, -- Meant to contain meaningful, short and stable values, e.g.; 'C' for 'Car'; 'B' for 'Boat'; 'P' for 'Plane'.
Name CHAR(30) NOT NULL,
--
CONSTRAINT Category_PK PRIMARY KEY (CategoryCode),
CONSTRAINT Category_AK UNIQUE (Name) -- ALTERNATE KEY.
);
CREATE TABLE Item ( -- Stands for the supertype.
ItemId INT NOT NULL,
OwnerId INT NOT NULL,
CategoryCode CHAR(1) NOT NULL, -- Denotes the subtype discriminator.
Foo CHAR(30) NOT NULL,
Bar CHAR(30) NOT NULL,
Baz CHAR(30) NOT NULL,
CreatedDateTime DATETIME NOT NULL,
--
CONSTRAINT Item_PK PRIMARY KEY (ItemId),
CONSTRAINT Item_to_Category_FK FOREIGN KEY (CategoryCode)
REFERENCES Category (CategoryCode),
CONSTRAINT Item_to_User_FK FOREIGN KEY (OwnerId)
REFERENCES UserProfile (UserId)
);
CREATE TABLE Car ( -- Represents one of the subtypes.
CarId INT NOT NULL, -- Must be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
Qux CHAR(30) NOT NULL,
Corge CHAR(30) NOT NULL,
--
CONSTRAINT Car_PK PRIMARY KEY (CarId),
CONSTRAINT Car_to_Item_FK FOREIGN KEY (CarId)
REFERENCES Item (ItemId)
);
CREATE TABLE Boat ( -- Stands for one of the subtypes.
BoatId INT NOT NULL, -- Must be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
Grault CHAR(30) NOT NULL,
Garply CHAR(30) NOT NULL,
Plugh CHAR(30) NOT NULL,
--
CONSTRAINT Boat_PK PRIMARY KEY (BoatId),
CONSTRAINT Boat_to_Item_FK FOREIGN KEY (BoatId)
REFERENCES Item (ItemId)
);
CREATE TABLE Plane ( -- Denotes one of the subtypes.
PlaneId INT NOT NULL, -- Must be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
Xyzzy CHAR(30) NOT NULL,
Thud CHAR(30) NOT NULL,
Wibble CHAR(30) NOT NULL,
Flob CHAR(30) NOT NULL,
--
CONSTRAINT Plane_PK PRIMARY KEY (PlaneId),
CONSTRAINT Plane_to_Item_PK FOREIGN KEY (PlaneId)
REFERENCES Item (ItemId)
);
Jak wykazano, typ nadrzędności i każdy z typów podrzędności jest reprezentowany przez odpowiednią tabelę podstawową .
Kolumny CarId, BoatIdi PlaneId, o ograniczonej jako PK w odpowiednich tabelach, pomoc w reprezentująca koncepcyjne poziomie stowarzyszenie jeden-do-jednego w drodze ograniczenia FK § że punkt do ItemIdkolumny, która jest ograniczona jako PK w Itemtabeli. Oznacza to, że w rzeczywistej „parze” zarówno wiersze nadtypu, jak i podtypu są identyfikowane przez tę samą wartość PK; dlatego warto o tym wspomnieć
- (a) dołączenie dodatkowej kolumny do przechowywania kontrolowanych przez system wartości zastępczych ‖ do (b) tabele oznaczające podtypy są (c) całkowicie zbędne .
§ Aby zapobiec problemom i błędom dotyczącym (szczególnie ZAGRANICZNYCH) KLUCZOWYCH definicji ograniczeń - sytuacji, o których wspominałeś w komentarzach - bardzo ważne jest, aby wziąć pod uwagę zależność istnienia występującą między różnymi dostępnymi tabelami, jak to pokazano w kolejność deklaracji tabel w strukturze DDL ekspozycyjnej, którą również dostarczyłem w tym SQL Fiddle .
‖ Np. Dołączenie dodatkowej kolumny z właściwością AUTO_INCREMENT do tabeli bazy danych zbudowanej na MySQL.
Uwagi dotyczące integralności i spójności
Bardzo ważne jest, aby zwrócić uwagę, że w środowisku biznesowym musisz (1) upewnić się, że każdy wiersz „nadtypu” jest zawsze uzupełniany przez odpowiadający mu odpowiednik „podtypu”, a z kolei (2) gwarantuje, że Wiersz „podtyp” jest zgodny z wartością zawartą w kolumnie „dyskryminator” wiersza „nadtyp”.
Egzekwowanie takich okoliczności w sposób deklaratywny byłoby bardzo eleganckie , ale, o ile mi wiadomo, żadna z głównych platform SQL nie zapewniła odpowiednich mechanizmów. Dlatego uciekając się do kodu proceduralnego w ramach ACID TRANSACTIONS, jest to całkiem wygodne, aby te warunki były zawsze spełnione w bazie danych. Inną opcją byłoby zatrudnienie wyzwalaczy, ale mają tendencję do robienia rzeczy nieporządnych, że tak powiem.
Deklarowanie przydatnych widoków
Mając logiczny projekt taki jak ten wyjaśniony powyżej, bardzo praktyczne byłoby utworzenie jednego lub więcej widoków, tj. Tabel pochodnych, które zawierają kolumny, które należą do dwóch lub więcej odpowiednich tabel podstawowych . W ten sposób możesz np. WYBRAĆ bezpośrednio z tych widoków bez konieczności zapisywania wszystkich JOINÓW za każdym razem, gdy musisz pobrać „połączone” informacje.
Przykładowe dane
W związku z tym powiedzmy, że tabele podstawowe są „zapełniane” przykładowymi danymi pokazanymi poniżej:
--
INSERT INTO UserProfile
(UserId, FirstName, LastName, BirthDate, GenderCode, Username, CreatedDateTime)
VALUES
(1, 'Edgar', 'Codd', '1923-08-19', 'M', 'ted.codd', CURDATE()),
(2, 'Michelangelo', 'Buonarroti', '1475-03-06', 'M', 'michelangelo', CURDATE()),
(3, 'Diego', 'Velázquez', '1599-06-06', 'M', 'd.velazquez', CURDATE());
INSERT INTO Category
(CategoryCode, Name)
VALUES
('C', 'Car'), ('B', 'Boat'), ('P', 'Plane');
-- 1. ‘Full’ Car INSERTion
-- 1.1
INSERT INTO Item
(ItemId, OwnerId, CategoryCode, Foo, Bar, Baz, CreatedDateTime)
VALUES
(1, 1, 'C', 'This datum', 'That datum', 'Other datum', CURDATE());
-- 1.2
INSERT INTO Car
(CarId, Qux, Corge)
VALUES
(1, 'Fantastic Car', 'Powerful engine pre-update!');
-- 2. ‘Full’ Boat INSERTion
-- 2.1
INSERT INTO Item
(ItemId, OwnerId, CategoryCode, Foo, Bar, Baz, CreatedDateTime)
VALUES
(2, 2, 'B', 'This datum', 'That datum', 'Other datum', CURDATE());
-- 2.2
INSERT INTO Boat
(BoatId, Grault, Garply, Plugh)
VALUES
(2, 'Excellent boat', 'Use it to sail', 'Everyday!');
-- 3 ‘Full’ Plane INSERTion
-- 3.1
INSERT INTO Item
(ItemId, OwnerId, CategoryCode, Foo, Bar, Baz, CreatedDateTime)
VALUES
(3, 3, 'P', 'This datum', 'That datum', 'Other datum', CURDATE());
-- 3.2
INSERT INTO Plane
(PlaneId, Xyzzy, Thud, Wibble, Flob)
VALUES
(3, 'Extraordinary plane', 'Traverses the sky', 'Free', 'Like a bird!');
--
Następnie, korzystne pogląd jest taki, który gromadzi kolumny z Item, Cari UserProfile:
--
CREATE VIEW CarAndOwner AS
SELECT C.CarId,
I.Foo,
I.Bar,
I.Baz,
C.Qux,
C.Corge,
U.FirstName AS OwnerFirstName,
U.LastName AS OwnerLastName
FROM Item I
JOIN Car C
ON C.CarId = I.ItemId
JOIN UserProfile U
ON U.UserId = I.OwnerId;
--
Oczywiście można zastosować podobne podejście, aby również WYBRAĆ „pełny” Boati Planeinformacje bezpośrednio z jednej tabeli (w tych przypadkach pochodnej).
Po tym -Jeśli nie masz nic o obecności znaków NULL w wynik zestawy- z następującą definicję widoku, można, na przykład, „zbierać” kolumny z tabel Item, Car, Boat, Planei UserProfile:
--
CREATE VIEW FullItemAndOwner AS
SELECT I.ItemId,
I.Foo, -- Common to all Categories.
I.Bar, -- Common to all Categories.
I.Baz, -- Common to all Categories.
IC.Name AS Category,
C.Qux, -- Applies to Cars only.
C.Corge, -- Applies to Cars only.
--
B.Grault, -- Applies to Boats only.
B.Garply, -- Applies to Boats only.
B.Plugh, -- Applies to Boats only.
--
P.Xyzzy, -- Applies to Planes only.
P.Thud, -- Applies to Planes only.
P.Wibble, -- Applies to Planes only.
P.Flob, -- Applies to Planes only.
U.FirstName AS OwnerFirstName,
U.LastName AS OwnerLastName
FROM Item I
JOIN Category IC
ON I.CategoryCode = IC.CategoryCode
LEFT JOIN Car C
ON C.CarId = I.ItemId
LEFT JOIN Boat B
ON B.BoatId = I.ItemId
LEFT JOIN Plane P
ON P.PlaneId = I.ItemId
JOIN UserProfile U
ON U.UserId = I.OwnerId;
--
Kod pokazanych tutaj widoków ma jedynie charakter poglądowy. Oczywiście wykonanie niektórych ćwiczeń testowych i modyfikacji może przyspieszyć (fizyczne) wykonanie dostępnych zapytań. Ponadto może być konieczne usunięcie lub dodanie kolumn do wspomnianych widoków, zgodnie z potrzebami firmy.
Przykładowe dane i wszystkie definicje widoków są włączone do tego SQL Fiddle , aby można je było obserwować „w akcji”.
Manipulowanie danymi: Kod aplikacji lub aliasy kolumn
Wykorzystanie kodu aplikacji (jeśli to rozumiesz przez „kod specyficzny dla serwera”) i aliasy kolumn to inne istotne kwestie, o których wspomniałeś w następnych komentarzach:
Udało mi się obejść [JOIN] problem z kodem specyficznym dla serwera, ale tak naprawdę nie chcę tego robić - i- dodawanie aliasów do wszystkich kolumn może być „stresujące”.
Bardzo dobrze wyjaśnione, dziękuję bardzo. Jednak, jak podejrzewałem, będę musiał manipulować zestawem wyników podczas wyświetlania wszystkich danych ze względu na podobieństwa z niektórymi kolumnami, ponieważ nie chcę używać kilku aliasów, aby zachować czystość instrukcji.
Wskazane jest wskazanie, że podczas korzystania z kodu aplikacji jest bardzo odpowiednim zasobem do obsługi prezentacji (lub graficznych) funkcji zestawów wyników, unikanie pobierania danych w poszczególnych wierszach ma ogromne znaczenie, aby zapobiec problemom z szybkością wykonywania. Celem powinno być „pobranie” odpowiednich zestawów danych w całości za pomocą solidnych instrumentów do manipulacji danymi dostarczanych przez (precyzyjnie) zestaw silnika platformy SQL, abyś mógł zoptymalizować zachowanie swojego systemu.
Co więcej, użycie aliasów do zmiany nazwy jednej lub więcej kolumn w pewnym zakresie może wydawać się stresujące, ale osobiście uważam taki zasób za bardzo potężne narzędzie, które pomaga (i) kontekstualizować oraz (ii) ujednoznacznić znaczenie i intencję przypisaną do danego kolumny; stąd jest to aspekt, który należy dokładnie rozważyć w odniesieniu do manipulacji danymi będącymi przedmiotem zainteresowania.
Podobne scenariusze
Równie dobrze możesz znaleźć pomoc dla tej serii postów i tej grupy postów, które zawierają moje spojrzenie na dwa inne przypadki, które zawierają skojarzenia typu podtyp z wzajemnie wykluczającymi się podtypami.
Zaproponowałem również rozwiązanie dla środowiska biznesowego obejmującego klaster nadtyp-podtyp, w którym podtypy nie wykluczają się wzajemnie w tej (nowszej) odpowiedzi .
Przypisy końcowe
1 Integration Definition for Information Modeling ( IDEF1X ) to wysoce godna polecenia technika modelowania danych, która została ustanowiona jako standard w grudniu 1993 r. Przez amerykański Narodowy Instytut Standardów i Technologii (NIST). Jest solidnie oparte na (a) niektóre z autorem prac teoretycznych przez jedynego twórcy tego modelu relacyjnego , czyli dr EF Codd ; na (b) pogląd na związek z bytem , opracowany przez dr PP Chen ; a także w (c) Logical Database Design Technique, stworzonej przez Roberta G. Browna.
2 W IDEF1X nazwa roli jest wyróżniającą etykietą przypisaną do właściwości FK (lub atrybutu) w celu wyrażenia znaczenia, które posiada w zakresie odpowiedniego typu jednostki.
3 Standard IDEF1X definiuje migrację klucza jako „Proces modelowania polegający na umieszczeniu klucza podstawowego elementu nadrzędnego lub podmiotu ogólnego welemenciepodrzędnym lub encji jako klucza obcego”.
Itemtabela zawieraCategoryCodekolumnę. Jak wspomniano w części zatytułowanej „Względy uczciwości i spójności”: