Tabela zamowiec, a statusy zamowienia

Witam,

Mam sobie tabele zamowienia. Musze, gdzies zapisac status zamowien. Mam ich 4, tj zamowienie wydrukowane, zamowienie spakowane, zamowienie wyslane etc. Nie chce mi sie zbyt dodawac tych kolumn do tabeli zamowienia, a z drugiej strony tez nie ma sensu tworzyc nowej tabeli i tworzyc relacje 1 do 1. Jakies pomysla? Szczegolnie, ze nie wiem czy wyszystkie emaila zawsze beada wysylane.

Kolumna status, 4 wartości - zależnie od liczby ustalasz jego status i modyfikujesz

Czy zamówienie może być wydrukowane i wysłane w tym samym czasie? Jeśli tak to polecam jednak oddzielne kolumny.

Tworzysz nową tabelę ze słownikiem statusów a do tabeli Zamówienie dodajesz pole ze statusem. Wtedy tworzysz statusy jakie chcesz i ile chcesz.

Tak jak powiedział @MiL:

CREATE TABLE status_zamowienia
(
                id TINYINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
                nazwa VARCHAR(30)
) ENGINE INNODB
;

CREATE TABLE zamowienia
(
                id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                status_id TINYINT UNSIGNED NOT NULL,
                .
                .
                .
                # Pozostałe kolumny
                KEY status_id (status_id)
                CONSTRAINT zamowienia_status_id FOREIGN KEY (status_id) REFERENCES status_zamowienia (id)
) ENGINE INNODB
;

Czyli mamy nową tabelę ze statusami, która jest słownikiem. W samej tabeli zamowienia tworzymy kolumnę z ID statusu i tworzymy relację za pomocą klucza obcego (tego robić nie trzeba, ale osobiście polecam korzystanie z kluczy obcych)

[Adam]

Moim zdaniem osobna tabela tylko skomplikuje zapytania. Jeśli każde zamówienie ma status, to naprawdę wygodniej jest dodać jedną kolumnę, niż potem robić złączenia.
Pozdrawiam

A co to będzie za kolumna? Gdzie będzie informacja jaki to status?

No oczywiście informacja będzie w tej kolumnie, po to proponuję ją dodać. Jeśli baza obsługuje typ ENUM to najlepiej zdfiniować taki typ. Jeśli nie obsługuje, to kolumna może przechowywać inta ze zbioru 1,2,3,4. Każdy int będzie oznaczał osobny status.

Poprawne rozwiązanie jest takie jak podałem. Można robić półśrodki w rodzaju jednej kolumny i trzymać w głowie co jaka liczba oznacza ale lepiej tego potem nikomu nie pokazywać.

W postgresie mam do tego celu typ ENUM, nic w glowie nie muszę trzymać. Czy enum jest w innych bazach to nie wiem.
Pozdrawiam

P.S.
W mysqlu też jest typ enum:
http://dev.mysql.com/doc/refman/5.7/en/enum.html

Tam jest opis jak zrobić odpowiedni enum w sqlserver:
http://stackoverflow.com/questions/1434298/sql-server-equivalent-to-mysql-enum-data-type

W sqlite proponują użyć referencji do pola z nazwą:
http://stackoverflow.com/questions/5299267/how-to-create-enum-type-in-sqlite

Gdy w większej aplikacji dużo danych wyciągnie się do osobnych tabelek, to z bazy mającej 20 tabel może zrobić się 60 tabel, a
zapytania w których łączono 3-4 tabele, rozrosną się do np. 8. Naprawdę definiowanie specjalnej tabeli do przechowywania
małej i w dodatku stałej ilości napisów, rzadko jest dobrym pomysłem. Tym bardziej jeśli baza wspomaga typ enum.

Generalnie ostatecznie użyłem relacji wiele-do-wielu. Tabele: zamówienia-tabela łącząca -statusy. Ale tylko dlatego, że mogę widzieć jakie dokładnie email były wysłane. Jak będę miał wartość w ENUM, nie wiem czy poprzednie emaile były wysłane czy nie. W moim przypadku muszę mieć pewność, że email wyszedł. Druga sprawa, że w takiej tabeli łączące, czy jak się tam zwie, mogę przechowywać takie dane jak czas wysłania email, czy status emaila etc. Najbardziej podoba mi się odpowiedz z ENUM i relacją jeden do wielu z tabelą statusy, bardzo trafny pomysł.

Czasami, jeśli wiadomo że w realcji wiele do wielu maksymalnie przypisujemy góra 2-3 elementy, też warto zastanowić się, nad dodaniem 2-3 kolumn. Czas wysłania e-maila też można trzymać w kolejnej kolumnie. Rozwiązanie które wybrałeś powszechnie uważa się za najbardziej eleganckie. Ale jeśli baza się rozrośnie w szerz i wzdłuż, to będziesz miał problemy wydajnościowe. Zazwyczaj nie ma różniczy czy odczytujemy dane z jednej tabeli, czy z trzech tabel. Ale, gdy w bazie pojawią się nowe tabele, to może się okazać, że złączenie ośmiu tabel działa znacznie dłużej niż pięciu. Ja bym dał wie kolumny z ENUM i trzecią z datą wysłania e-mial, choć to nie jest najbardziej eleganckie rozwiązanie.

Pozdrawiam

Ja też nie lubię tego odwołania do nowej tabeli, szczególnie jeżeli chodzi wyłącznie o wysłane emaile. Ale tych emaili wysyłam 4. To co 4 kolumny utworzyć? Sama tabela zamówienia już jest bardzo dużą. z 35-45 kolumn. A tak to wydaje się przejrzyściej.

Ja nie powiem Ci co masz zrobić :) Samemu musisz przeanalizować swój problem. Tylko mówię, jakie mogą być konsekwencje eleganckiej struktury: bardziej skomplikowane zapytania i mniejsza wydajność. Z kolei konsekwencje dodawania kolumn mogą być takie, że jeśli po czasie będzie od 0 do 10 statusów, to będzie trzeba dodać aż 10 kolumn :) Wybór należy do Ciebie, lepiej znasz swój problem.

Pozdrawiam

@artur_bredzki - zgadzam się. I dzięki wszystkim za komentarze:)

Wygląda na to, że już wybrałeś rozwiązanie - wrzucę jeszcze notkę dla potomnych.

Dlaczego ENUM to zły pomysł?
W tej chwili masz 4 możliwe statusy, więc robisz ENUM z czterema opcjami. Załóżmy, że Twój klient pracuje tak przez 2 lata i potem dochodzi do wniosku, że potrzebuje nowy status. Przez te 2 lata zebrało się 2 mln zamówień (bo klient dobrze prosperuje ;)). Żeby dodać nowy status musisz wykonać ALTER na tabeli, który będzie trwał bardzo długo. Nie można tego nazwać elastyczną strukturą. Dodatkowo jeśli chciałbyś wyświetlić wszystkie możliwe statusy w polu typu select na stronie to jedyna sensowna droga to wypisanie ich w kodzie "na sztywno".

Tworząc tabelę słownikową nic nie tracisz na wydajności - gdybyś jej nie miał to wówczas miałbyś jedno z proponowanych rozwiązań, czyli samą kolumnę int - jeśli nie chcesz nikt Ci nie każe robić JOINa do tabeli słownikowej, ale za to gdy ktoś "odziedziczy" Twój kod to od razu będzie wiedział co dany numerek statusu oznacza (a i Ty się sam w tym nie pogubisz). Tak jak wspomniałem - na wydajności nie tracisz nic, nawet jeśli do każdego zapytania dopiszesz JOIN do słownika to będzie to bardzo szybkie złączenie - niezauważalne nawet przy wyciąganiu dużej ilości danych z dużej tabeli.

Pozdrawiam

rozwiązanie z dodatkową kolumną na każdy status jest takie sobie również dlatego, że przeważnie status w danym momencie jest tylko jeden więc mamy wartość "1" tylko w jednej kolumnie, w pozostałych mamy null - jeśli statusów jest kilka to mamy n-1 nulli w każdym rekordzie, gdzie n to ilość statusów

kchteam napisał(a):

Wygląda na to, że już wybrałeś rozwiązanie - wrzucę jeszcze notkę dla potomnych.

Dlaczego ENUM to zły pomysł?
W tej chwili masz 4 możliwe statusy, więc robisz ENUM z czterema opcjami. Załóżmy, że Twój klient pracuje tak przez 2 lata i potem dochodzi do wniosku, że potrzebuje nowy status. Przez te 2 lata zebrało się 2 mln zamówień (bo klient dobrze prosperuje ;)). Żeby dodać nowy status musisz wykonać ALTER na tabeli, który będzie trwał bardzo długo. Nie można tego nazwać elastyczną strukturą. Dodatkowo jeśli chciałbyś wyświetlić wszystkie możliwe statusy w polu typu select na stronie to jedyna sensowna droga to wypisanie ich w kodzie "na sztywno".

Tworząc tabelę słownikową nic nie tracisz na wydajności - gdybyś jej nie miał to wówczas miałbyś jedno z proponowanych rozwiązań, czyli samą kolumnę int - jeśli nie chcesz nikt Ci nie każe robić JOINa do tabeli słownikowej, ale za to gdy ktoś "odziedziczy" Twój kod to od razu będzie wiedział co dany numerek statusu oznacza (a i Ty się sam w tym nie pogubisz). Tak jak wspomniałem - na wydajności nie tracisz nic, nawet jeśli do każdego zapytania dopiszesz JOIN do słownika to będzie to bardzo szybkie złączenie - niezauważalne nawet przy wyciąganiu dużej ilości danych z dużej tabeli.

Pozdrawiam

Ja ekspertem od baz ne jestem, ale:

Gdy testuję, to każdy JOIN o ileś spowalnia.

Używanie tablicy słownikowej i operowanie na intach (bez join) nie jest zbyt wygodne.

Listę typów z enum można selectem pozyskać, nic nie trzeba na szytywno w kodzie wpisywać.

Dodanie nowego pola do enum też jest proste.

http://stackoverflow.com/questions/1616123/sql-query-to-get-all-values-a-enum-can-have/1616161

https://www.postgresql.org/docs/9.1/static/sql-altertype.html
[ALTER TYPE ... ADD VALUE (the form that adds a new value to an enum type) cannot be executed inside a transaction block.]

Poświęciłem się, zrobiłem eksperyment. W tym przykładzie mam znacznie większy słownik, bo tabelę z krajami. Ilość krajów
wynosi 300, więc użycie tabeli słownikowej jest znacznie bardziej wskazane, ale czy na pewno?

Przykład zapytania

select * from users as u join countries as c on c.id = u.id_country order by c.name,u.name offset 10000 limit 10 ;
   id    | id_country | name | id  | name  
---------+------------+------+-----+-------
 1486520 |        227 | HGFC | 227 | AAEFB
  749251 |        227 | HGFC | 227 | AAEFB
 1113106 |        227 | HGFD | 227 | AAEFB
 2630231 |        227 | HGFD | 227 | AAEFB
 2421530 |        227 | HGFD | 227 | AAEFB
 2200889 |        227 | HGFD | 227 | AAEFB
 1843388 |        227 | HGFE | 227 | AAEFB
 1334618 |        227 | HGFE | 227 | AAEFB
 2908654 |        227 | HGFE | 227 | AAEFB
 1400578 |        227 | HGFH | 227 | AAEFB
Time: 64214,685 ms

Czas jak widać, 64 sekundy.

Teraz mała sztuczka, dodaję kolumnę do użytkowników z nazwą kraju i dodaję indeks:

alter table users add column country  varchar(255); 
update users as u set country = (select name from countries as c where c.id = u.id_country);
create index users_cn on users(country,name);

select * from users as u order by u.country,u.name offset 10000 limit 10 ;
   id    | id_country | name | country 
---------+------------+------+---------
  749251 |        227 | HGFC | AAEFB
 1486520 |        227 | HGFC | AAEFB
 1113106 |        227 | HGFD | AAEFB
 2200889 |        227 | HGFD | AAEFB
 2421530 |        227 | HGFD | AAEFB
 2630231 |        227 | HGFD | AAEFB
 1334618 |        227 | HGFE | AAEFB
 1843388 |        227 | HGFE | AAEFB
 2908654 |        227 | HGFE | AAEFB
  724483 |        227 | HGFH | AAEFB
(10 rows)
Time: 38,939 ms

Czas 38ms, przyspieszenie 1650 razy - ale join nie spowalnia zapytań ;-)
Może łatwych zapytań nie spowalnia.

Przez dodanie pola varchar(255) rozmiar tabel znacznie się zwiększył, no ale cóż, nie ma optymalizacji za darmo.

Pozdrawiam

Skoro mowa o MySQLl, a nie postgreSQL, gdzie jest osobny TYP ENUM i kolumne tworzy się danego TYPU (co również ma swoje zalety normalizacyjuen - mozliwość użycia w kilku tabelach), to co poczniesz, gdy użytkownik wpadnie na pomysł... "a dajmy te statusy po angielsku". ALTER TABLE dla np 3 mln rekordów? Możesz się nie doczekać...
Nie, żebym jakoś specjalnie narzekał na MySQL, ale właśnie z ostatnim projektem przechodzę z My do post...

Dlatego pisałem gdzieś wyżej, że ta technika nadaje się jedynie do danych, które często się nie zmieniają.

Ja mam w bazie tabelę Zamowienie gdzie jest ponad 5mln rekordów, zamówienia mają kilka pól słownikowych z danymi wyciągniętymi do osobnych tabel i wszystko działa wydajnie. Dodatkowo można dodawać nowe statusy, zmieniać nazwy itp. Zauważ że słownik ma zwykle niewiele pozycji więc złączenie go z inną tabelą nie powoduje widocznego spadku wydajności.

@artur_bredzki wynik testu, który przeprowadziłeś sugeruje niewłaściwe indeksowanie. Czy możesz wrzucić CREATE tabel users i countries?
Zwróć uwagę na to, że sortujesz wynik zapytania po nazwie kraju. Widać, że indeks na tę kolumnę założyłeś w przykładzie z ENUM (create index users_cn on users(country,name);), ale czy taki indeks istniał w tabeli słownikowej? Ponadto czy w tabeli users był założony indeks na country_id?
Tak duży spadek wydajności raczej nie jest spowodowany JOINem do słownika, a niewłaściwym indeksowaniem obu tabel.
Co do ALTERa - linki, które podałeś traktują o Postgresie, a nie o MySQL.

kchteam napisał(a):

@artur_bredzki wynik testu, który przeprowadziłeś sugeruje niewłaściwe indeksowanie.

Dlatego piszę, że ja nie umiem lepiej zaindeksować, a nie że w ogóle nie da się lepiej. Na razie nikt mi nie
pokazał, że można lepiej zaindeksować.

Czy możesz wrzucić CREATE tabel users i countries?

Tak, cały skrypt jest ten sam, wraz z kodem generującym przykładowe dane, 300 krajów, 3mln użytkowników, wystarczy odpalić:

\timing off
\pset pager off
 
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
 
 
\c postgres;
DROP DATABASE IF EXISTS test1;
DROP ROLE IF EXISTS test1;
 
CREATE ROLE test1 PASSWORD 'test1' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
CREATE DATABASE test1 OWNER test1 ENCODING 'UTF-8';
\c test1;
set role test1;
 
 
CREATE TABLE countries (
    id   serial                NOT NULL,
    name character varying(10) NOT NULL
);
 
 
CREATE TABLE users (
    id   serial                NOT NULL,
    id_country integer         NOT NULL,
    name character varying(10) NOT NULL
);
 
 
create or replace function random_string(length integer) returns text as 
$$
declare
  chars text[] := '{A,B,C,D,E,F,G,H}';
  result text := '';
  i integer := 0;
begin
  for i in 1..length loop
    result := result || chars[1+random()*(array_length(chars, 1)-1)];
  end loop;
  return result;
end;
$$ language plpgsql;
 
 
create or replace function mk_countries(length integer) returns void as 
$$
declare
  i integer;
begin
  for i in 1..length loop
    execute 'insert into countries (name) values( random_string(5) )' ;
  end loop;
end;
$$ language plpgsql;
 
 
create or replace function mk_users(length integer) returns void as 
$$
declare
  i integer;
begin
  for i in 1..length loop
    execute 'insert into users (id_country,name) values( 1+random()*299 , random_string(4) )';
  end loop;
end;
$$ language plpgsql;
 
begin;
select mk_countries(300);
select mk_users(3000000);
commit;
 
ALTER TABLE ONLY countries ADD CONSTRAINT countries_pkey PRIMARY KEY (id);
ALTER TABLE ONLY users ADD CONSTRAINT users_pkey PRIMARY KEY (id);
ALTER TABLE ONLY users ADD CONSTRAINT users_id_country_fkey FOREIGN KEY (id_country) REFERENCES countries(id);
 
 
CREATE INDEX countries_name ON countries(name);
CREATE INDEX users_name ON users(name);
CREATE INDEX users_id_country ON users(id_country);
 
 
SELECT i.relname as indname,
       i.relowner as indowner,
       idx.indrelid::regclass,
       am.amname as indam,
       idx.indkey,
       ARRAY(
       SELECT pg_get_indexdef(idx.indexrelid, k + 1, true)
       FROM generate_subscripts(idx.indkey, 1) as k
       ORDER BY k
       ) as indkey_names,
       idx.indexprs IS NOT NULL as indexprs,
       idx.indpred IS NOT NULL as indpred
FROM   pg_index as idx
JOIN   pg_class as i
ON     i.oid = idx.indexrelid
JOIN   pg_am as am
ON     i.relam = am.oid
JOIN   pg_namespace as ns
ON     ns.oid = i.relnamespace
AND    ns.nspname = ANY(current_schemas(false))
ORDER BY i.relname;
 
 
\timing on
 
 
--przykłady zapytań
select distinct u.name, c.name from users as u join countries as c on u.id_country = c.id order by u.name offset 10000 limit 30;
select u.name, c.name from users as u join countries as c on u.id_country = c.id order by u.name,c.name offset 10000 limit 30;
select u.name, c.name from users as u join countries as c on u.id_country = c.id order by c.name,u.name offset 10000 limit 30;
select distinct u.name from users as u order by u.name offset 10000 limit 30;
select distinct u.name, c.name from users as u join countries as c on u.id_country = c.id order by c.name,u.name offset 10000 limit 30;
select distinct u.name, c.name from users as u join countries as c on u.id_country = c.id order by u.name,c.name offset 10000 limit 30;

Zwróć uwagę na to, że sortujesz wynik zapytania po nazwie kraju. Widać, że indeks na tę kolumnę założyłeś w przykładzie z ENUM (create index users_cn on users(country,name);), ale czy taki indeks istniał w tabeli słownikowej? Ponadto czy w tabeli users był założony indeks na country_id?

Powiem tak, dużo kombinowalem, nie udalo się. Krajów jest 300, moim zdaniem indeks zadziała od np. 50tys rekordów - w zalezności od innych szczegółów.

Tak duży spadek wydajności raczej nie jest spowodowany JOINem do słownika, a niewłaściwym indeksowaniem obu tabel.

Może masz rację, nie mówię że nie, tym bardziej że ekspertem nie jestem. Ja tylko chciałbym to zobaczyć na działającym przykładzie.

Co do ALTERa - linki, które podałeś traktują o Postgresie, a nie o MySQL.

Tak, ale w mysqlu powinny być odpowiedniki.

Pozdrawiam

@artur_bredzki: Niestety nie bardzo mam możliwość szybkiego sprawdzenia Twojego skryptu na Postgresie. Poniżej nieco okrojony, ale analogiczny skrypt dla MySQL. W tym przypadku czas wykonania zapytania SELECT (w zależności od ustawionego offsetu) jest na poziomie ~50ms.

CREATE TABLE countries_d (
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL,
    INDEX `name` (`name`)
) ENGINE INNODB
;


CREATE TABLE users_d (
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    id_country INT UNSIGNED NOT NULL ,
    `name` VARCHAR(10) NOT NULL,
    INDEX id_country (id_country),
    CONSTRAINT users_d_id_country FOREIGN KEY (id_country) REFERENCES countries_d(id)
) ENGINE INNODB
;

DELIMITER $$

CREATE FUNCTION `generateRandomString`(wyraz INT, minimum INT, roznica INT, caps TINYINT) RETURNS TEXT CHARSET utf8 DETERMINISTIC
BEGIN
                DECLARE i INT DEFAULT 0;
                DECLARE w INT;
                DECLARE s TEXT;
                DECLARE q INT DEFAULT 32;
                SET s = '';
                               
    IF caps = 1 THEN SET q = 0;
    END IF;
                               
                               WHILE i < wyraz DO
      SET w = minimum + ROUND(RAND()*roznica);
      WHILE w > 0 DO
        SET s = CONCAT(s, CHAR((RAND()*25) + (65+q)));
        SET w = w - 1;
      END WHILE;
      SET s = CONCAT(s, ' ');
      SET i = i + 1;
    END WHILE;
                               
                RETURN s;
END$$

CREATE PROCEDURE mkCountry()
BEGIN
                DECLARE i INT DEFAULT 0;

                WHILE i < 300  DO
                
                               INSERT INTO countries_d (`name`) VALUES (generateRandomString(1, 4, 4, 0));
                
                SET i := i + 1;
                END WHILE;
END$$

CREATE PROCEDURE mkUser()
BEGIN
                DECLARE i INT DEFAULT 0;

                WHILE i < 1000000  DO
                
                               INSERT INTO users_d (id_country, `name`) VALUES (1 + RAND() * 299, generateRandomString(1, 4, 4, 0));
                
                SET i := i + 1;
                END WHILE;
END$$

DELIMITER ;


CALL mkCountry()
;
CALL mkUser()
;

SELECT
                *
FROM
                users_d U
                JOIN countries_d C ON C.id = U.id_country
ORDER BY
                C.name
LIMIT 10000, 30
;

kchteam napisał(a):

@artur_bredzki: Niestety nie bardzo mam możliwość szybkiego sprawdzenia Twojego skryptu na Postgresie. Poniżej nieco okrojony, ale analogiczny skrypt dla MySQL. W tym przypadku czas wykonania zapytania SELECT (w zależności od ustawionego offsetu) jest na poziomie ~50ms.

SELECT
                *
FROM
                users_d U
                JOIN countries_d C ON C.id = U.id_country
ORDER BY
                C.name
LIMIT 10000, 30

U mnie w postgresie to zapytanie trwa 19ms z tabelą 3mln użytkowników.
Ale gdy dam order by c.name,u.name
to już trwa 64sekundy.

Uruchamiam to na laptopie i3.

Cały skrypt do bazy postgres u mnie wykonuje się 10 minut.

Pozdrawiam