Przegląd schematu/poziom normalizacji/optymalizacja zapytań


1

Mam więc stosunkowo skomplikowaną bazę danych, którą projektuję - w zasadzie pełną wewnętrzną konfigurację księgową.W jednej sekcji mam pewne obawy, że nadmiernie normalizuję;„katalog” - przykładowa sekcja dla pracowników, ale dla klientów, dostawców itp. będzie taka sama podstawowa rzecz

Zasadniczo directory przechowuje informacje istotne dla wszystkich typów, niezależnie od tego, czy jest to klient, pracownik czy x, pracownicy mogą mieć dowolną liczbę adresów, adresów e-mail itp.

Używanie MySQL 5.6.12, PHP/PDO (nie to, na co mam dostęp, naprawdę ma znaczenie dla pytania)

Odpowiednie wywołania tworzenia tabeli:

CREATE TABLE IF NOT EXISTS 'directory' (
    'directory_id' int(11) NOT NULL AUTO_INCREMENT,
    'name_display' varchar(255) NOT NULL,
    'type' tinyint(2) unsigned NOT NULL COMMENT '0: human, 1: unincorporated, 2: incorporated',
    'visibility' varchar(255) NOT NULL,
    'notes' text NOT NULL,
    PRIMARY KEY ('directory_id')
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS 'directory_addresses' (
    'directory_id' int(11) NOT NULL,
    'address_id' int(11) NOT NULL AUTO_INCREMENT,
    'type' int(11) NOT NULL,
    'country' int(11) NOT NULL,
    'region' int(11) NOT NULL,
    'city' varchar(255) NOT NULL,
    'postalcode' varchar(10) NOT NULL,
    'line1' varchar(255) NOT NULL,
    'line2' varchar(255) NOT NULL,
    'version' int(11) NOT NULL,
    'primary' tinyint(1) NOT NULL,
    'active' tinyint(1) NOT NULL DEFAULT '1',
    PRIMARY KEY ('address_id'),
    KEY 'fk_type' ('type'),
    KEY 'fk_country' ('country')
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


    CREATE TABLE IF NOT EXISTS 'directory_email' (
    'directory_id' int(11) NOT NULL,
    'email_id' int(11) NOT NULL AUTO_INCREMENT,
    'primary' tinyint(1) NOT NULL,
    'email' varchar(255) NOT NULL,
    PRIMARY KEY ('email_id')
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS 'directory_employee' (
    'directory_id' int(11) NOT NULL,
    'id' int(11) NOT NULL AUTO_INCREMENT,
    'employee_id' varchar(10) NOT NULL,
    'start_date' date NOT NULL,
    'end_date' date DEFAULT NULL,
    'department' int(11) NOT NULL,
    'notes' text NOT NULL,
    PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS 'directory_employee_payrate' (
    'employee_id' int(11) NOT NULL,
    'payrate_id' int(11) NOT NULL AUTO_INCREMENT,
    'start_date' date NOT NULL,
    'end_date' date DEFAULT NULL,
    'rate' decimal(10,2) NOT NULL,
    PRIMARY KEY ('payrate_id')
) ENGINE=InnoDB DEFAULT CHARSET=latin

W przypadku strony przeglądu listy pracowników powstaje zapytanie o:

Select
     directory_employee.directory_id,
     directory_employee.employee_id,
     directory_employee.department,
     directory_employee.start_date,
     directory_employee.end_date,
     directory_employee.notes,
     directory_employee_payrate.rate,
     directory.name_display,
     directory_email.email,
     directory_phone.phone,
     directory_addresses.line1,
     directory_addresses.line2,
     directory_addresses.city,
     directory_addresses.region,
     directory_addresses.postalcode,
     directory_addresses.country
FROM directory_employee
LEFT JOIN directory_employee_payrate ON
     directory_employee_payrate.employee_id = directory_employee.id
     AND directory_employee_payrate.end_date IS NULL
LEFT JOIN directory ON
     directory.directory_id = directory_employee.directory_id
LEFT JOIN directory_addresses ON
     directory_addresses.directory_id = directory_employee.directory_id
     AND directory_addresses.primary = 1
LEFT JOIN directory_email ON
     directory_email.directory_id = directory_employee.directory_id
     AND directory_email.primary = 1
LEFT JOIN directory_phone ON
     directory_phone.directory_id = directory_employee.directory_id
     AND directory_phone.primary = 1

Co jest wprawdzie nieco DŁUGIE!

Więc moje pytania zasadniczo sprowadzają się do:

  • Czy to wygląda na rozsądny schemat?
  • Czy jest on nadmiernie znormalizowany?(Jeśli tak, to co poleciłbyś zamiast tego - szeregowane tablice?)
  • Jeśli jest to rozsądny schemat, czy są jakieś optymalizacje, które można by zastosować w odniesieniu do tej instrukcji Select ?
  • Wszelkie ulepszenia schematu zalecane poza głównym punktem tego pytania?(Mam FK - po prostu nie ustawiłem ich wszystkich/skopiowałem instrukcji definicji).
  0

Niektóre z nich dotyczą nadmiernej normalizacji: [1] (http://stackoverflow.com/questions/47711), [2] (http://stackoverflow.com/questions/292797).Można tworzyć wspólne widoki dla powtarzających się „długich” połączeń.Dla każdego połączenia na dir_id zazwyczaj chcesz mieć indeks na dir_id lub pk prowadzący z dir_id.„Plan wyjaśniania” powinien być w stanie pomóc zweryfikować wykorzystanie pk/wskazań.Unikaj używania lewego łączenia jako zamiennika połączenia wewnętrznego.Mam nadzieję, że ktoś może przejrzeć bardziej konkretnie dla Ciebie. 03 lut. 142014-02-03 09:00:04

  0

Nie wierzę, że używam Left Join zamiast Inner join gdziekolwiek tam - przypuszczam, że mógłbym go użyć tylko w tabeli katalogu - ponieważ ten jeden powinien mieć takie same + dodatkowe wiersze jak pracownicy;ale inni nie muszą istnieć, aby rzucić jakiekolwiek wiersze od pracowników, którzy nie mają e-maila (np.);co nie jest tym, czego chcę. 03 lut. 142014-02-03 16:45:43

  0

Przeczytałem oba te linki i myślę, że są one dobrze znormalizowane, ale chciałem, aby ktoś z większą ilością (My) doświadczenia/wiedzy SQL mógł je obejrzeć i powiedzieć, co myślą.Dziękujemy za sugestie. 03 lut. 142014-02-03 16:47:22

1

W większości przypadków jest to dobry projekt schematu dla wewnętrznych elementów systemu księgowego.Normalizacja daje dużą elastyczność w tego rodzaju scenariuszach, a to, co zrobiłeś, wydaje się właściwe.

To powiedziawszy, zakwestionowałem wybór tabeli katalogów.Zdarzają się przypadki, w których coś takiego może być właściwe, ale w takich przypadkach istnieje zazwyczaj pewien rodzaj wspólnego wątku - wiele wspólnych pól lub częsta potrzeba agregowania danych we wszystkich typach (np. Wiele typów klient, który składa zamówienia za pośrednictwem wspólnego systemu).Gdybym miał tylko mały fragment, który tu pokazałeś, powiedziałbym, że został on nadmiernie znormalizowany.Wspominasz jednak, że masz podobne struktury dla klientów, dostawców i tym podobnych, a biorąc pod uwagę, że jest to system księgowy, zgaduję, że łączysz to wszystko ze schematem księgi głównej.Jeśli ta analiza jest poprawna, tabela katalogów staje się wspólnym łączem między wszystkimi różnymi rodzajami odbiorców w systemie, co jest konieczne, a zatem jest dobrym projektem.

(Przepraszam za tę chaotyczną odpowiedź, ale normalizacja jest sztuką. Miałem nadzieję przekazać mój proces myślenia w odpowiedzi.)

Pytałeś także o inne optymalizacje.Najważniejszą rzeczą, jaką możesz zrobić, aby pomóc silnikowi relacyjnemu w dowolnym RDBMS, jest uwzględnienie dobrych indeksów.Wygląda na to, że masz dobry początek, może więcej - masz podstawowe klucze, które, jak zakładam, mają indeksy.Wspominasz również klucze obce, które mogą pomóc przynajmniej niektórym silnikom zoptymalizować zapytania.Moją specjalnością nie jest MySQL, więc nie mogę mówić bezpośrednio o technicznych aspektach optymalizacji jego silnika, ale ogólnie rzecz biorąc, prawdopodobnie będzie dobrze.Zwróć uwagę na wydajność zapytań, sprawdź plany wykonania/wyjaśnij plany i dodaj dodatkowe indeksy, jeśli to konieczne.

  0

Tak Klienci, Dostawcy itd. Wszyscy odwołują się do tabeli katalogów jako danych podstawowych/iw niektórych przypadkach pojedyncza jednostka może mieć wiele ról, więc wydawało się, że najlepszym sposobem uniknięcia duplikacji.Dziękuję za szczegółową odpowiedź (a ja wolę wędrować ze szczegółami niż po prostu bez wyjaśnienia). 06 lut. 142014-02-06 21:15:18