Czy kiedykolwiek można używać list w relacyjnej bazie danych?

Próbowałem zaprojektować bazę danych, aby pasowała do koncepcji projektu i natknąłem się na coś, co wydaje się być przedmiotem gorącej dyskusji. Przeczytałem kilka artykułów i kilka odpowiedzi przepełnienia stosu, które stwierdzają, że nigdy (lub prawie nigdy) nie jest dobrze przechowywać listy identyfikatorów lub podobnych w polu - wszystkie dane powinny być relacyjne itp.

Problem, na który wpadam, polega na tym, że próbuję przypisać zadanie. Ludzie będą tworzyć zadania, przypisywać je do wielu osób i zapisywać je w bazie danych.

Oczywiście, jeśli zapisam te zadania indywidualnie w „Osobie”, będę musiał mieć dziesiątki fikcyjnych kolumn „TaskID” i zarządzać nimi mikro, ponieważ powiedzmy, że do jednej osoby można przypisać od 0 do 100 zadań.

Z drugiej strony, jeśli zapiszę zadania w tabeli „Zadania”, będę musiał mieć dziesiątki fikcyjnych kolumn „PersonID” i mikrozarządzać nimi - taki sam problem jak poprzednio.

W przypadku takiego problemu, czy w porządku jest zapisanie listy identyfikatorów przyjmujących taką czy inną formę, czy też nie myślę o innym sposobie, w jaki można to osiągnąć bez łamania zasad?

Kluczowym słowem i kluczową koncepcją, którą musisz zbadać, jest normalizacja bazy danych .

Zamiast dodawać informacje o przydziałach do tabel osób lub zadań, dodajesz nową tabelę z tymi informacjami o zadaniu, z odpowiednimi relacjami.

Przykład: masz następujące tabele:

Osoby:

+ −−−− + −−−−−−−−−−−− +
| ID | Imię |
+ ==== + =========== +
| 1 | Alfred |
| 2 | Jebediah |
| 3 | Jakub |
| 4 | Ezechiel |
+ −−−− + −−−−−−−−−−−− +

Zadania:

+ −−−− + −−−−−−−−−−−−−−−−−−−−− +
| ID | Imię |
+ ==== + ==================== +
| 1 | Nakarm kurczaki |
| 2 | Pług |
| 3 | Dojenie krów |
| 4 | Podnieś stodołę |
+ −−−− + +

Następnie utworzysz trzeci stół z przydziałami. Poniższa tabela modelowałaby relacje między ludźmi a zadaniami:

+ −−−− + −−−−−−−−−−−− + −−−−−−−−− +
| ID | PersonId | TaskId |
+ ==== + =========== + ========= +
| 1 | 1 | 3 |
| 2 | 3 | 2 |
| 3 | 2 | 1 |
| 4 | 1 | 4 |
+ −−−− + −−−−−−−−−−−− + −−−−−−−−− +

Mielibyśmy wtedy ograniczenie klucza obcego, tak że baza danych wymusiłaby, że PersonId i TaskIds muszą być ważnymi identyfikatorami dla tych obcych elementów. Dla pierwszego rzędu, widzimy PersonId is 1, więc Alfred , jest przypisany TaskId 3, dojenia krów .

To, co powinieneś zobaczyć, to to, że możesz mieć tak mało lub tyle zadań na zadanie lub na osobę, ile chcesz. W tym przykładzie Ezekielowi nie przypisano żadnych zadań, a Alfredowi przypisano 2. Jeśli masz jedno zadanie ze 100 osobami, wykonanie SELECT PersonId from Assignments WHERE TaskId=<whatever>;da 100 wierszy z przypisanymi różnymi osobami. Możesz WHEREna PersonId znaleźć wszystkie zadania przypisane do tej osoby.

Jeśli chcesz zwrócić zapytania zastępujące identyfikatory nazwami i zadaniami, musisz nauczyć się ŁĄCZYĆ tabele.

Zadajesz tutaj dwa pytania.

Najpierw pytasz, czy możesz przechowywać listy zserializowane w kolumnie. Tak w porządku. Jeśli twój projekt tego wymaga. Przykładem mogą być składniki produktu na stronie katalogu, w których nie chcesz próbować śledzić każdego składnika indywidualnie.

Niestety twoje drugie pytanie opisuje scenariusz, w którym powinieneś wybrać bardziej relacyjne podejście. Potrzebujesz 3 stołów. Jeden dla ludzi, jeden dla zadań i jeden, który utrzymuje listę zadań przypisanych do poszczególnych osób. Ten ostatni byłby pionowy, jeden wiersz na kombinację osoba / zadanie, z kolumnami na klucz podstawowy, identyfikator zadania i identyfikator osoby.

To, co opisujesz, znane jest jako relacja „wiele do wielu”, w twoim przypadku pomiędzy Personi Task. Zazwyczaj jest implementowany przy użyciu trzeciej tabeli, czasami nazywanej tabelą „link” lub „odsyłacz”. Na przykład:

create table person (
    person_id integer primary key,
    ...
);

create table task (
    task_id integer primary key,
    ...
);

create table person_task_xref (
    person_id integer not null,
    task_id integer not null,
    primary key (person_id, task_id),
    foreign key (person_id) references person (person_id),
    foreign key (task_id) references task (task_id)
);

... nigdy (lub prawie nigdy) nie jest dobrze przechowywać w polu listę identyfikatorów lub tym podobnych

Jedynym razem może przechowywać więcej niż jeden element danych w jednym polu jest gdy to pole jest tylko kiedykolwiek stosowany jako pojedynczy podmiot i jest nigdy uważane za składa się z tych mniejszych elementów. Przykładem może być obraz przechowywany w polu BLOB. Składa się z wielu mniejszych elementów (bajtów), ale te nic nie znaczą dla bazy danych i mogą być używane tylko razem (i wyglądają ładnie dla użytkownika końcowego).

Ponieważ „lista” z definicji składa się z mniejszych elementów (elementów), w tym przypadku tak nie jest i należy normalizować dane.

... jeśli zapiszę te zadania indywidualnie w „Osobie”, będę musiał mieć dziesiątki fikcyjnych kolumn „TaskID”…

Nie. Będziesz miał kilka wierszy w tabeli przecięcia (aka Słaby byt) między osobą a zadaniem. Bazy danych są naprawdę dobre w pracy z wieloma wierszami; w rzeczywistości są dość śmieciami przy pracy z wieloma [powtarzanymi] kolumnami.

Ładny, jasny przykład podany przez whatsisname.

Może być uzasadniony w niektórych wstępnie obliczonych polach.

Jeśli niektóre z twoich zapytań są drogie i zdecydujesz się na pola wstępnie obliczone aktualizowane automatycznie przy użyciu wyzwalaczy bazy danych, może być uzasadnione, aby zachować listy w kolumnie.

Na przykład w interfejsie użytkownika chcesz wyświetlić tę listę za pomocą widoku siatki, w którym każdy wiersz może otworzyć pełne szczegóły (z kompletnymi listami) po dwukrotnym kliknięciu:

REGISTERED USER LIST
+------------------+----------------------------------------------------+
|Name              |Top 3 most visited tags                             |
+==================+====================================================+
|Peter             |Design, Fitness, Gifts                              |
+------------------+----------------------------------------------------+
|Lucy              |Fashion, Gifts, Lifestyle                           |
+------------------+----------------------------------------------------+

Druga kolumna jest aktualizowana przez wyzwalacz, gdy klient odwiedza nowy artykuł lub zaplanowane zadanie.

Możesz udostępnić takie pole nawet do wyszukiwania (jako zwykły tekst).

W takich przypadkach prowadzenie list jest uzasadnione. Musisz tylko rozważyć przypadek przekroczenia maksymalnej długości pola.

Ponadto, jeśli korzystasz z Microsoft Access, oferowane pola wielowartościowe są kolejnym specjalnym przypadkiem użycia. Obsługują one Twoje listy w polu automatycznie.

Ale zawsze możesz wrócić do standardowej znormalizowanej formy przedstawionej w innych odpowiedziach.

Podsumowanie: Normalne formy bazy danych to model teoretyczny wymagany do zrozumienia ważnych aspektów modelowania danych. Ale oczywiście normalizacja nie uwzględnia wydajności ani innych kosztów odzyskiwania danych. Jest poza zakresem tego modelu teoretycznego. Jednak praktyczna implementacja często wymaga przechowywania list lub innych wstępnie obliczonych (i kontrolowanych) duplikatów.

W świetle powyższego, w praktycznej realizacji, czy wolelibyśmy, aby zapytanie opierało się na doskonałej normalnej formie i działało 20 sekund, lub równoważne zapytanie polegało na wstępnie obliczonych wartościach, które zajmują 0,08 s? Nikt nie lubi, aby ich oprogramowanie było oskarżane o powolność.

Biorąc pod uwagę dwie tabele; nazywamy je Osoba i Zadanie, każdy z własnym ID (PersonID, TaskID) ... podstawową ideą jest stworzenie trzeciej tabeli, aby je połączyć. Nazwiemy ten stół PersonToTask. Przynajmniej powinien mieć swój własny identyfikator, a także dwa inne. Więc jeśli chodzi o przypisanie kogoś do zadania; nie będziesz już musiał aktualizować tabeli Person, po prostu WSTAW nowy wiersz do PersonToTaskTable. A konserwacja staje się łatwiejsza - potrzeba usunięcia zadania staje się po prostu DELETE na podstawie TaskID, koniec aktualizacji tabeli Person i powiązanej z nią analizy

CREATE TABLE dbo.PersonToTask (
    pttID INT IDENTITY(1,1) NOT NULL,
    PersonID INT NULL,
    TaskID   INT NULL
)

CREATE PROCEDURE dbo.Task_Assigned (@PersonID INT, @TaskID INT)
AS
BEGIN
    INSERT PersonToTask (PersonID, TaskID)
    VALUES (@PersonID, @TaskID)
END

CREATE PROCEDURE dbo.Task_Deleted (@TaskID INT)
AS
BEGIN
    DELETE PersonToTask  WHERE TaskID = @TaskID
    DELETE Task          WHERE TaskID = @TaskID
END

Co powiesz na prosty raport lub kto jest przypisany do zadania?

CREATE PROCEDURE dbo.Task_CurrentAssigned (@TaskID INT)
AS
BEGIN
    SELECT PersonName
    FROM   dbo.Person
    WHERE  PersonID IN (SELECT PersonID FROM dbo.PersonToTask WHERE TaskID = @TaskID)
END

Oczywiście możesz zrobić znacznie więcej; TimeReport można zrobić, jeśli dodasz pola DateTime dla TaskAssigned i TaskCompleted. Wszystko zależy od Ciebie

Może działać, jeśli powiesz, że masz klucze podstawowe czytelne dla człowieka i chcesz listę zadań # bez konieczności zajmowania się pionową naturą struktury tabeli. czyli o wiele łatwiejszy do odczytania pierwszy stół.

------------------------  
Employee Name | Task 
Jack          |  1,2,5
Jill          |  4,6,7
------------------------

------------------------  
Employee Name | Task 
Jack          |  1
Jack          |  2
Jack          |  5
Jill          |  4
Jill          |  6
Jill          |  7
------------------------

Pytanie brzmiałoby zatem: czy lista zadań powinna być przechowywana lub generowana na żądanie, co w dużej mierze zależy od wymagań, takich jak: jak często lista jest potrzebna, jak dokładna jest liczba wierszy danych, jak dane zostaną wykorzystane itp. .. po czym należy dokonać analizy kompromisów pod kątem doświadczenia użytkownika i spełnienia wymagań.

Na przykład porównanie czasu potrzebnego do przywołania 2 wierszy w porównaniu z uruchomieniem zapytania, które wygeneruje 2 wiersze. Jeśli zajmuje to dużo czasu, a użytkownik nie potrzebuje najbardziej aktualnej listy (* oczekującej mniej niż 1 zmiany dziennie), można ją zapisać.

Lub jeśli użytkownik potrzebuje historycznego zapisu przypisanych mu zadań, miałoby to również sens, gdyby lista była przechowywana. To naprawdę zależy od tego, co robisz, nigdy nie mów nigdy.

Bierzesz coś, co powinno być innym stołem, obracając go o 90 stopni i konstruując w inny stół.

To tak, jakbyś miał tabelę zamówień, w której masz itemProdcode1, itemQuantity1, itemPrice1 ... itemProdcode37, itemQuantity37, itemPrice37. Oprócz niezręczności w obsłudze programowej możesz zagwarantować, że jutro ktoś będzie chciał zamówić 38 rzeczy.

Zrobiłbym to tylko po swojemu, jeśli „lista” nie jest tak naprawdę listą, tzn. Gdzie stoi jako całość, a każdy pojedynczy element zamówienia nie odnosi się do jakiegoś jasnego i niezależnego bytu. W takim przypadku wystarczy umieścić to wszystko w wystarczająco dużym typie danych.

Zatem zamówienie jest listą, lista materiałów jest listą (lub listą list, co byłoby jeszcze bardziej koszmarem do wdrożenia „z boku”). Ale notatka / komentarz i wiersz nie są.

Jeśli nie jest to w porządku, to dość źle, że każda witryna Wordpress ma kiedykolwiek listę w wp_usermeta z wp_capabilities w jednym rzędzie, lista odrzuconych_wp_pointers w jednym rzędzie i inne ...

W rzeczywistości w takich przypadkach może być lepsza prędkość, ponieważ prawie zawsze będziesz chciał listy . Ale Wordpress nie jest doskonałym przykładem najlepszych praktyk.