Tak, identyfikacja powiązań lub relacji wiele-do-wielu (M: N dla zwięzłości) jest sytuacją, z którą praktykujący bazę danych spotykają się dość często podczas tworzenia schematu pojęciowego. Powiązania tych współczynników liczebności powstają w środowiskach biznesowych o bardzo odmiennym charakterze, a gdy są odpowiednio reprezentowane na poziomie logicznym za pomocą np. Rozwiązania SQL-DDL, nie wprowadzają szkodliwych redundancji.
W ten sposób celem modelowania bazy danych powinno być odzwierciedlenie istotnych cech interesującego kontekstu biznesowego z dużą precyzją ; dlatego, jeśli poprawnie zidentyfikujesz, że istnieje wiele powiązań M: N, musisz je wyrazić w (a) schemacie pojęciowym, a także w (b) odpowiednich deklaracjach na poziomie logicznym, bez względu na to, ile takich powiązań - lub dowolnych inne - należy zająć się stosunkami liczności.
Zasady biznesowe
Dostarczyłeś dobrze sformułowane pytanie, a także wyjaśniłeś, że baza danych, nad którą pracujesz, jest czysto hipotetyczna, co jest istotną kwestią, ponieważ uważam, że scenariusz biznesowy w „prawdziwym świecie”, taki jak ten rozważany, byłby znacznie szerszy i dlatego implikują bardziej złożone wymagania informacyjne.
Postanowiłem (1) wprowadzić kilka modyfikacji i rozszerzeń reguł biznesowych, które podałeś, aby (2) stworzyć bardziej opisowy schemat koncepcyjny - choć wciąż raczej hipotetyczny -. Oto niektóre z formuł, które zestawiłem:
- Strona 1 jest albo osoba lub organizacja
- Partia została sklasyfikowana przez dokładnie jeden lat PartyType
- PartyType klasyfikuje zero-jeden-or-wiele Strony
- Organizacja rozwija zero-jeden-lub-wiele Produkty
- Produkt jest albo systemu lub gier
- Produkt jest klasyfikowany przez dokładnie jeden lat Producttype
- Systemu jest skatalogowana przez dokładnie jeden lat SystemType
- W Grę można grać za pośrednictwem systemów jeden do wielu
- System jest wykorzystywany do gry jeden-do-wielu Games
- Gra została sklasyfikowana przez zero-jeden-lub-wiele Gatunki
- Gatunek klasyfikuje zero-jeden-or-wiele gier
- A Produkt pochodzi jeden-do-wielu miejscach pracy
- Praca jest spełniony przez zero-jeden-or-wielu ludzi , którzy grają rolę z współpracownikami
- Osoba jest Współpracownik w zero-jeden-or-wielu miejscach pracy
1 Strona jest terminem używanym w kontekście prawnym, odnoszącym się do osoby lub grupy osób, które składają się na jeden podmiot, więc ta nazwa nadaje się do reprezentowania Ludzi i Organizacji .
Schemat IDEF1X
Następnie utworzyłem diagram IDEF1X 2 pokazany na rysunku 1 (kliknij link, aby zobaczyć go w wyższej rozdzielczości), konsolidując w jednym urządzeniu graficznym powyższe reguły biznesowe (wraz z kilkoma innymi, które wydają się istotne):
2 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 Narodowy Instytut Norm i Technologii Stanów Zjednoczonych (NIST). Opiera się na (a) wczesnym materiale teoretycznym autorstwa jedynego twórcy modelu relacyjnego, tj. Dr EF Codda; na (b) widok danych relacji jednostka , opracowany przez dr PP Chen ; a także w (c) Logical Database Design Technique, stworzonej przez Roberta G. Browna.
Jak widać, przedstawiłem tylko trzy powiązania M: N za pomocą odpowiednich typów bytów asocjacyjnych , tj .:
- Współpracownik
- SystemGame
- GameGenre
Wśród innych aspektów istnieją dwie różne struktury podtypu , w których:
Osoba i Organizacja to wzajemnie wykluczające się podtypy bytu Partii , ich nadtyp bytu
Produkt jest nadtypem Systemu i Gry , które z kolei są wzajemnie wykluczającymi się podtypami
Jeśli nie jesteś zaznajomiony ze skojarzeniami typu podtyp, możesz znaleźć pomoc, np. Moje odpowiedzi na pytania zatytułowane:
Ilustracyjny logiczny układ SQL-DDL
Sukcesywnie musimy upewnić się, że na poziomie logicznym:
- Każdy typ jednostki jest reprezentowany przez indywidualną tabelę podstawową
- Każda pojedyncza właściwość odpowiedniego typu encji jest oznaczona przez określoną kolumnę
- Dokładny typ danych jest ustalony dla każdej kolumny , aby zapewnić, że wszystkie zawarte w nim wartości należą do określonego i dobrze zdefiniowanego zestawu, czy to INT, DATETIME, CHAR itp. (Oczywiście, gdy używa się np. Firebird lub PostgreSQL , możesz chcieć zatrudnić silniejsze domeny DOMAIN)
- Wiele ograniczeń konfiguruje się (deklaratywnie), aby zagwarantować, że twierdzenia w postaci wierszy zachowane we wszystkich tabelach są zgodne z regułami biznesowymi określonymi na poziomie koncepcyjnym
Zadeklarowałem więc następujący układ DDL na podstawie wcześniej pokazanego schematu IDEF1X:
CREATE TABLE PartyType ( -- Stands for an independent entity type.
PartyTypeCode CHAR(1) NOT NULL, -- To retain 'P' or 'O'.
Name CHAR(30) NOT NULL, -- To keep 'Person' or 'Organization'.
--
CONSTRAINT PartyType_PK PRIMARY KEY (PartyTypeCode)
);
CREATE TABLE Party ( -- Represents an entity supertype.
PartyId INT NOT NULL,
PartyTypeCode CHAR(1) NOT NULL, -- To hold the value that indicates the type of the row denoting the complementary subtype occurrence: either 'P' for 'Person' or 'O' for 'Organization'.
CreatedDateTime TIMESTAMP NOT NULL,
--
CONSTRAINT Party_PK PRIMARY KEY (PartyId),
CONSTRAINT PartyToPartyType_FK FOREIGN KEY (PartyTypeCode)
REFERENCES PartyType (PartyTypeCode)
);
CREATE TABLE Person ( -- Denotes an entity subtype.
PersonId INT NOT NULL, -- To be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
FirstName CHAR(30) NOT NULL,
LastName CHAR(30) NOT NULL,
GenderCode CHAR(3) NOT NULL,
BirthDate DATE NOT NULL,
--
CONSTRAINT Person_PK PRIMARY KEY (PersonId),
CONSTRAINT Person_AK UNIQUE (FirstName, LastName, GenderCode, BirthDate), -- Composite ALTERNATE KEY.
CONSTRAINT PersonToParty_FK FOREIGN KEY (PersonId)
REFERENCES Party (PartyId)
);
CREATE TABLE Organization ( -- Stands for an entity subtype.
OrganizationId INT NOT NULL, -- To be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
Name CHAR(30) NOT NULL,
FoundingDate DATE NOT NULL,
--
CONSTRAINT Organization_PK PRIMARY KEY (OrganizationId),
CONSTRAINT Organization_AK UNIQUE (Name), -- Single-column ALTERNATE KEY.
CONSTRAINT OrganizationToParty_FK FOREIGN KEY (OrganizationId)
REFERENCES Party (PartyId)
);
CREATE TABLE ProductType ( -- Represents an independent entity type.
ProductTypeCode CHAR(1) NOT NULL, -- To enclose the values 'S' and 'G' in the corresponding rows.
Name CHAR(30) NOT NULL, -- To comprise the values 'System' and 'Person' in the respective rows.
--
CONSTRAINT ProductType_PK PRIMARY KEY (ProductTypeCode)
);
CREATE TABLE Product ( -- Denotes an entity supertype.
OrganizationId INT NOT NULL,
ProductNumber INT NOT NULL,
ProductTypeCode CHAR(1) NOT NULL, -- To keep the value that indicates the type of the row denoting the complementary subtype occurrence: either 'S' for 'System' or 'G' for 'Game'.
CreatedDateTime DATETIME NOT NULL,
--
CONSTRAINT Product_PK PRIMARY KEY (OrganizationId, ProductNumber), -- Composite PRIMARY KEY.
CONSTRAINT ProductToOrganization_FK FOREIGN KEY (OrganizationId)
REFERENCES Organization (OrganizationId),
CONSTRAINT ProductToProductType_FK FOREIGN KEY (ProductTypeCode)
REFERENCES ProductType (ProductTypeCode)
);
CREATE TABLE SystemType ( -- Stands for an independent entity type.
SystemTypeCode CHAR(1) NOT NULL,
Name CHAR(30) NOT NULL,
--
CONSTRAINT SystemType_PK PRIMARY KEY (SystemTypeCode)
);
CREATE TABLE MySystem ( -- Represents a dependent entity type.
OrganizationId INT NOT NULL, -- To be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
SystemNumber INT NOT NULL,
SystemTypeCode CHAR(1) NOT NULL,
ParticularColumn CHAR(30) NOT NULL,
--
CONSTRAINT System_PK PRIMARY KEY (OrganizationId, SystemNumber),
CONSTRAINT SystemToProduct_FK FOREIGN KEY (OrganizationId, SystemNumber)
REFERENCES Product (OrganizationId, ProductNumber),
CONSTRAINT SystemToSystemType_FK FOREIGN KEY (SystemTypeCode)
REFERENCES SystemType (SystemTypeCode)
);
CREATE TABLE Game ( -- Denotes an entity subtype.
OrganizationId INT NOT NULL, -- To be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
GameNumber INT NOT NULL,
SpecificColumn CHAR(30) NOT NULL,
--
CONSTRAINT Game_PK PRIMARY KEY (OrganizationId, GameNumber),
CONSTRAINT GameToProduct_FK FOREIGN KEY (OrganizationId, GameNumber)
REFERENCES Product (OrganizationId, ProductNumber)
);
CREATE TABLE Genre ( -- Stands for an independent entity type.
GenreNumber INT NOT NULL,
Name CHAR(30) NOT NULL,
Description CHAR(90) NOT NULL,
--
CONSTRAINT Genre_PK PRIMARY KEY (GenreNumber),
CONSTRAINT Genre_AK1 UNIQUE (Name),
CONSTRAINT Genre_AK2 UNIQUE (Description)
);
CREATE TABLE SystemGame ( -- Represents an associative entity type or M:N association.
SystemOrganizationId INT NOT NULL,
SystemNumber INT NOT NULL,
GameOrganizationId INT NOT NULL,
GameNumber INT NOT NULL,
CreatedDateTime DATETIME NOT NULL,
--
CONSTRAINT SystemGame_PK PRIMARY KEY (SystemOrganizationId, SystemNumber, GameOrganizationId, GameNumber), -- Composite PRIMARY KEY.
CONSTRAINT SystemGameToSystem_FK FOREIGN KEY (SystemOrganizationId, SystemNumber) -- Multi-column FOREIGN KEY.
REFERENCES MySystem (OrganizationId, SystemNumber),
CONSTRAINT SystemGameToGame_FK FOREIGN KEY (SystemOrganizationId, GameNumber) -- Multi-column FOREIGN KEY.
REFERENCES Game (OrganizationId, GameNumber)
);
CREATE TABLE GameGenre ( -- Denotes an associative entity type or M:N association.
GameOrganizationId INT NOT NULL,
GameNumber INT NOT NULL,
GenreNumber INT NOT NULL,
CreatedDateTime DATETIME NOT NULL,
--
CONSTRAINT GameGenre_PK PRIMARY KEY (GameOrganizationId, GameNumber, GenreNumber), -- Composite PRIMARY KEY.
CONSTRAINT GameGenreToGame_FK FOREIGN KEY (GameOrganizationId, GameNumber)
REFERENCES Game (OrganizationId, GameNumber), -- Multi-column FOREIGN KEY.
CONSTRAINT GameGenreToGenre_FK FOREIGN KEY (GenreNumber)
REFERENCES Genre (GenreNumber)
);
CREATE TABLE Job ( -- Stands for an associative entity type or M:N association.
OrganizationId INT NOT NULL,
ProductNumber INT NOT NULL,
JobNumber INT NOT NULL,
Title CHAR(30) NOT NULL,
CreatedDateTime DATETIME NOT NULL,
--
CONSTRAINT Job_PK PRIMARY KEY (OrganizationId, ProductNumber, JobNumber), -- Composite PRIMARY KEY.
CONSTRAINT Job_AK UNIQUE (Title), -- Single-column ALTERNATE KEY.
CONSTRAINT JobToProduct_FK FOREIGN KEY (OrganizationId, ProductNumber) -- Multi-column FOREIGN KEY.
REFERENCES Product (OrganizationId, ProductNumber)
);
CREATE TABLE Collaborator ( -- Represents an associative entity type or M:N association.
CollaboratorId INT NOT NULL,
OrganizationId INT NOT NULL,
ProductNumber INT NOT NULL,
JobNumber INT NOT NULL,
AssignedDateTime DATETIME NOT NULL,
--
CONSTRAINT Collaborator_PK PRIMARY KEY (CollaboratorId, OrganizationId, ProductNumber, JobNumber), -- Composite PRIMARY KEY.
CONSTRAINT CollaboratorToPerson_FK FOREIGN KEY (CollaboratorId)
REFERENCES Person (PersonId),
CONSTRAINT CollaboratorToJob_FK FOREIGN KEY (OrganizationId, ProductNumber, JobNumber) -- Multi-column FOREIGN KEY.
REFERENCES Job (OrganizationId, ProductNumber, JobNumber)
);
Warto podkreślić, że istnieją deklaracje złożonych ograniczeń KLUCZ PODSTAWOWYCH w kilku tabelach, które reprezentują hierarchię połączeń, które zachodzą między typami jednostek pojęciowych, co może być bardzo korzystne w odniesieniu do wyszukiwania danych, np. Wyrażając SELECT operacje zawierające klauzule JOIN w celu uzyskania tabel pochodnych .
Tak, (i) każde powiązanie M: N oraz (ii) każdy z powiązanych typów jednostek jest oznaczony przez (iii) odpowiednią tabelę w logicznej strukturze DDL, więc zwróć szczególną uwagę na podstawowe KLUCZOWE i ZAGRANICZNE ograniczenia (oraz uwagi, które pozostawiłem jako komentarze) tabel reprezentujących te elementy pojęciowe, ponieważ pomagają one zapewnić, że połączenia między odpowiednimi wierszami spełniają obowiązujące współczynniki liczności.
Zastosowanie kluczy kompozytowych zostało wprowadzone przez dr EF Codda od samego początku paradygmatu relacyjnego, jak wykazano w przykładach, które zawarł w swoim seminarium z 1970 r. Zatytułowanym A Relational Model for Large Shared Data Banks (który dokładnie przedstawia również najbardziej elegancka metoda obsługi pojęciowych skojarzeń M: N).
Kładę się do db <> skrzypce i SQL Fiddle , zarówno w systemie Microsoft SQL Server 2014, dzięki czemu struktura może być przetestowany „w akcji”.
Normalizacja
Normalizacja to procedura na poziomie logicznym, która w zasadzie oznacza:
Eliminacja kolumn nieatomowych za pomocą pierwszej normalnej formy, dzięki czemu manipulacja danymi i ich zwężenie są znacznie łatwiejsze do opanowania dzięki podrzędnemu językowi danych (np. SQL).
Pozbycie się niepożądanych zależności między kolumnami określonej tabeli dzięki kolejnym normalnym formom, aby uniknąć anomalii aktualizacji .
Oczywiście należy wziąć pod uwagę znaczenie, jakie niosą omawiane tabele i kolumny.
Lubię myśleć o normalizacji jako o teście opartym na nauce, że projektant stosuje się do odpowiednich elementów, gdy wyznaczy stabilne ustawienie na poziomie logicznym w celu ustalenia, czy jego elementy są zgodne z każdą z normalnych form, czy nie. Następnie, w razie potrzeby, projektant podejmuje odpowiednie działania korygujące.
Nadmiar
W modelu relacyjnym, podczas gdy powielanie wartości zawartych w kolumnach jest nie tylko dopuszczalne, ale oczekiwane , duplikaty wierszy są zabronione . Do tego stopnia, o ile widzę, duplikaty wierszy i inne rodzaje szkodliwych redundancji są zapobiegane we wszystkich tabelach zawartych w logicznym układzie ujawnionym wcześniej, być może chciałbyś wyjaśnić swoje obawy w tym zakresie.
W każdym razie z pewnością możesz (a) ocenić własną strukturę za pomocą zwykłych formularzy, aby określić, czy spełnia ona wymagania i (b) zmodyfikować ją, jeśli to konieczne.
Powiązane zasoby
- W tej serii postów przedstawiam rozważania na temat prostego skojarzenia M: N, które mogą ze sobą powiązane wystąpienia dwóch różnych typów bytów.
- W tym drugim proponuję podejście do obsługi wystąpienia konstrukcji „Zestawienia materiałów” lub „Eksplozji części”, w której opisuję, jak połączyć różne wystąpienia tego samego typu bytu.
Trójskładnikowe stowarzyszenia
Jest jeszcze jeden ważny aspekt, który poruszyłeś za pomocą komentarzy (opublikowanych w usuniętej teraz odpowiedzi):
Za każdym razem, gdy próbuję zbudować most, elementy tego mostu mają również wiele do wielu, mam wrażenie, że jest to niedozwolone lub przynajmniej zniechęcone.
Ta okoliczność wydaje się wskazywać, że jedna z twoich obaw dotyczy pojęć trójskładnikowych . Zasadniczo, tego rodzaju powiązania powstają, gdy istnieje (1) relacja obejmująca (2) dwa inne relacje, innymi słowy „relacja między relacjami” - również typowa sytuacja, ponieważ relacja jest odrębną jednostką -.
Ustalenia te, jeśli są odpowiednio zarządzane, również nie powodują szkodliwych zwolnień. I tak, jeśli istnieje pewien przypadek użycia, w którym identyfikujesz, że takie relacje występują między typami bytów w „świecie rzeczywistym”, musisz (i) modelować i (ii) deklarować je z dokładnością na poziomie logicznym.
- Oto pytanie i odpowiedź , kiedy analizujemy dziedzinę dyskursu na temat ankiet , która obejmuje przykład trójskładnikowego skojarzenia.
- W tym bardzo dobra odpowiedź , @Ypercube prezentuje schemat i odpowiednią strukturę DDL za interesującą kształcie diamentu relacji , która jest bardzo podobna do tej klasy scenariuszy.