Wątpliwości przy projektowaniu bazy danych

Postanowiłem utworzyć topic bo mam kilka zaptań związanych z "dobrymi praktykami" przy tworzeniu bazy danych..

Oto one:

1. Jak powinny być nazywane tabele - w liczbie pojedynczej czy mnogiej ? (gdzieś doczytałem, że w mnogiej)
2. Kwestia adresów zamieszkania i jego podzialu na kolumny.. np. Wojska polskiego 15a/8 00-222 Warszawa - jak to podzielić.. Raczej wiadomo jest aby wydzielić na kolumny nazwę ulicy i numer domu, kod miejscowości oraz miejscowość.. Ale czy opłaca się rozdzielać sam numer na nr bloku, mieszkania, klatki (15a/8)? (raczej nie widzę w tym sensu.. no chyba, że byłoby to wskazane ze względu na jakąś specyfike..). I jeszcze jedno - czy lepiej od razu zaprojektować osobne tabele dla kodów i miejscowości i potem używać referencji, czy może to nie jest istatne przy średniej wielkości tabel (np. 300 osób) ?
3. Czy INT(11) zajmuje 11 bajtów i to oznacza, że można tam wpisać maksymalnie 11 znaków? Bo w takim razie int64 mimo iż zajmuje tylko 8 bajtów może pomieścić większą cyfrę..
4. Kwestia nazywania id.. lepiej pisać IdUser czy userID ? (pewnie jak to woli, ale chciałbym usłyszeć opinie..)

Ok, na razie tyle pytań/wątpliwości przychodzi mi do głowy. Jakbyście mieli dodatkowe tego typu uwagi to chętnie je sobie przeczytam :)

1. tak się przyjęło - po polsku nazwiesz tabelę kontrahenci natomiast pola w niej w liczbie pojedynczej - np. kontrahent_id, tak samo po angielsku - tabela users, pole user_id
2. nie wydziela się numeru mieszkania do osobnego pola (tak samo jak nie robi się kolumny nr domu typu numerycznego) chyba, że ze specyfiki projektu jasno to wynika
3. int(11) zajmuje 11 bajtów i można tam wpisać max 2^11. Natomiast char(11) także zajmuje 11 bajtów ale można tam wpisać tylko 11 znaków
4. user_id jest bardziej naturalne. BTW ja jestem zwolennikiem oddzielania członów nazwy podkreśleniem _ bo nie wszystkie bazy zachowują wielkość liter w nazwach obiektów i nawet jak wpiszesz userId to potem baza może to pokazywać jako USERID co jest mniej czytelne niż USER_ID

Edit do pkt 3:
to działa podobnie jak varchar(n) - int(n) może przyjąć n cyfr. W postgresie to jest aktualnie "The actual storage requirement is two bytes for each group of four decimal digits, plus five to eight bytes overhead.". Pewnie w różnych bazach może być z tym różnie

To jeszcze się zapytam jak nazywać (w j. angielskim) tabele które miałyby taką relacje: osoby (persons) ---- karty_a_osoby ..... karty (cards).. Jak nazywać takie tabele ?

person_card ?? :)

abrakadaber napisał(a):

2. nie wydziela się numeru mieszkania do osobnego pola (tak samo jak nie robi się kolumny nr domu typu numerycznego) chyba, że ze specyfiki projektu jasno to wynika

Jeśli korzysta się ze słownika kodów, to wskazane jest wyodrębnienie tych dwóch pól. Niektóre kody pocztowe obowiązują np. tylko dla parzystych numerów domów...

abrakadaber napisał(a):

3. int(11) zajmuje 11 bajtów i można tam wpisać max 2^11. Natomiast char(11) także zajmuje 11 bajtów ale można tam wpisać tylko 11 znaków
   Oczywiście za wyjątkiem MySQL, gdzie do INT(2) można wpisać dowolną liczbę całkowitą... :)

Marcin.Miga napisał(a):

Oczywiście za wyjątkiem MySQL, gdzie do INT(2) można wpisać dowolną liczbę całkowitą... :)

Podczepie się pod ten punkt (chodz za reszte porad również dzięki..), bo ja właśnie działam na MySQL (a w zamiarach mam jeszcze baze Oracle).. To o ile dobrze rozumiem w INT(1) mogę wpisać np. cały PESEL ? Ale to przejdzie tylko w MySQL, Oracle już na to nie pójdzie? :P

I jeszcze jedna kwestia się pojawiła.. Bo od dłuższego czasu polenizowałem a teraz to już się kłóce z kumplem nad rozwiązaniem pewnej kwestii..

Chodzi o taką sytuacje: ja proponuje tabele users z kolumną is_admin gdzie jest oznaczenie administratora.. Kumpel caly czas się upiera aby zrobić taką **samą **tabele jak users (te same kolumny) i wypiera się głównie tym, że kolumna is_admin będzie praktycznie pusta (oprócz dwóch wpisów) i że to nie potrzebne marnowanie pamięci..

Ja z tego co wiem, i co widziałem na kilku wdrożonych systemach zawsze było to rozwiązane flagą.. Ja chciałbym udowodnić mu, że moje to jest "optymalne/lepsze" rozwiązanie tylko za bardzo nie potrafię to uargumentować.. ? Co możecie powiedzieć w tej kwestii?

ps. z tego co doczytałem to NULL zajmuje tyle i dany typ kolumny, czyli np. INT(1) zajmowałby 1bajt dla NULLa? (chodzi tu o mysql i oracle?)

tym, ile zajmuje pole w tabeli zaczniesz się przejmować jak Twoja baza osiągnie rozmiar 1TB. Baza ma (w większości przypadków) przede wszystkim trzymać dane bez zagrożenia, że zostaną utracone, być tak zorganizowana aby dało się w niej zapisać i jednocześnie odczytać wszystkie (no może jakieś 99%) te dane, dla których została stworzona oraz ma być relatywnie szybka. Tzn ma działać tak szybko abyśmy byli zadowoleni. To czy ona będzie zajmować 100GB czy 120GB to przy dzisiejszej technice jest sprawa pięcio czy nawet sześciorzędna.

Poprawcie mnie jeśli się myle, albo coś się zmieniło...
MySQL nie widzi różnicy pomiędzy wartością null a pustym stringiem?

oracle też nie - jak wstawiasz pusty string to oracle tam wstawia null

Kolejne pytanie - gdzie w tabeli umiejscawiać kolumne z kluczem refejencyjny ? Od razu za kluczym głównym czy na końcu tabeli ?

Kolejność nie ma znaczenia. Możesz sobie stworzyć tabele Adresy: [ID_Adresu, Lokal, Dom, FK_Osoba] pamiętając przy tym aby FK_Osoba miała ten sam typ danych co w tabeli Osoba klucz główny i potem dać alter table...

mikajlo napisał(a):

Raczej wiadomo jest aby wydzielić na kolumny nazwę ulicy i numer domu, kod miejscowości oraz miejscowość..

Czasem warto ulice mieć w tabeli, a nie jako pole adresu.

Ale czy opłaca się rozdzielać sam numer na nr bloku, mieszkania, klatki (15a/8)?

A co to jest numer bloku lub klatki?
Czasami cały blok ma numer w ramach ulicy, czasami klatka. Ale jakaś wewnątrzosiedlowa numeracja bloków i klatek nie ma znaczenia. Jeśli masz adres np. "ul. Szatańska 66a/15c", to 66a jest numerem nieruchomości, a 15c numerem mieszkania. Wszystko jedno, co to tu jest klatką, a co blokiem.

mikajlo napisał(a):

Chodzi o taką sytuacje: ja proponuje tabele users z kolumną is_admin gdzie jest oznaczenie administratora.. Kumpel caly czas się upiera aby zrobić taką **samą **tabele jak users (te same kolumny) i wypiera się głównie tym, że kolumna is_admin będzie praktycznie pusta (oprócz dwóch wpisów) i że to nie potrzebne marnowanie pamięci..

Ja z tego co wiem, i co widziałem na kilku wdrożonych systemach zawsze było to rozwiązane flagą.. Ja chciałbym udowodnić mu, że moje to jest "optymalne/lepsze" rozwiązanie tylko za bardzo nie potrafię to uargumentować.. ? Co możecie powiedzieć w tej kwestii?

Metadane drugiej tabeli zajmą przecież więcej niż "stracicie" na tej jednej kolumnie.
W prostym rozwiązaniu dodaje się pole, tak jak Ty chcesz zrobić. W wersji bardziej skomplikowanej masz tabele: Users, Roles i UserRoles, która łączy użytkowników z ich rolami w systemie (bo przecież ról może być więcej niż tylko zwykły użytkownik i administrator).

abrakadaber napisał(a):

tym, ile zajmuje pole w tabeli zaczniesz się przejmować jak Twoja baza osiągnie rozmiar 1TB. Baza ma (w większości przypadków) przede wszystkim trzymać dane bez zagrożenia, że zostaną utracone, być tak zorganizowana aby dało się w niej zapisać i jednocześnie odczytać wszystkie (no może jakieś 99%) te dane, dla których została stworzona oraz ma być relatywnie szybka.

Powodzenia przy robieniu pełnego backupu dużej bazy. Jeszcze więcej powodzenia przy odzyskiwaniu takiej bazy z sześciu backupów różnicowych i siódmego pełnego. Fakt, że to dość rzadka sytuacja, ale im większa baza tym dłuższy downtime, a tym samym więcej niezadowolonych użytkowników i mniejszy procent uptime, a to może oznaczać niezadowolenie klientów ($$$) i kary ($$$).
baza ma być szybka, przejrzysta i jak najmniejsza.

co to znaczy że da się zapisać i odczytać 99% danych z bazy? ma być zawsze 100%.

@autor: proponuję jeszcze nazywać pola w taki sposób, żeby po wrzuceniu do jakiegoś mappera (EF, (n)Hibernate, ...) wygenerowane klasy i ich pola miały nazwy zgodne z językiem, w którym będą używane. a więc w MSSQL dla C# nie powinny nazywać się ID_osoba, tylko UserId (unikamy polskich nazw gdzie się tylko da), a klucze główne powinny mieć nazwę po prostu Id. generalnie w tabeli Users nie powinno być pól User*, bo przecież już w nazwie tabeli mamy określone, jakiego typu to byt.

ŁF napisał(a):

abrakadaber napisał(a):

tym, ile zajmuje pole w tabeli zaczniesz się przejmować jak Twoja baza osiągnie rozmiar 1TB. Baza ma (w większości przypadków) przede wszystkim trzymać dane bez zagrożenia, że zostaną utracone, być tak zorganizowana aby dało się w niej zapisać i jednocześnie odczytać wszystkie (no może jakieś 99%) te dane, dla których została stworzona oraz ma być relatywnie szybka.

Powodzenia przy robieniu pełnego backupu dużej bazy. Jeszcze więcej powodzenia przy odzyskiwaniu takiej bazy z sześciu backupów różnicowych i siódmego pełnego. Fakt, że to dość rzadka sytuacja, ale im większa baza tym dłuższy downtime, a tym samym więcej niezadowolonych użytkowników i mniejszy procent uptime, a to może oznaczać niezadowolenie klientów ($$$) i kary ($$$).
baza ma być szybka, przejrzysta i jak najmniejsza.

aż się prosi aby Ci odpisać, że jak chcesz mieć małą bazę to zabroń userom zapisywać czegokolwiek do niej... A od takich sytuacji jest serwer zapasowy, który może być słabszy, mieć gorsze wyniki ale działający. Pewnie, że to są koszty ale albo ponosimy koszty na dodatkowe zabezpieczenia albo tracimy w razie przestoju. Coś za coś. BTW ciekaw jestem jak się ma to twoje jak najmniejsza do szybka bo często robi się tak, że kosztem redundancji danych osiąga się większą szybkość zapytań. Tu jak widać też albo jedno albo drugie. To tak jak z tym przysłowiowym mechanikiem: "do wyboru masz: szybko, tanio, dobrze. Wybierz tylko dwie".

co to znaczy że da się zapisać i odczytać 99% danych z bazy? ma być zawsze 100%.
Jeszcze się nie spotkałem z systemem, który w momencie projektowania przewidział wszelkie przypadki jakie się mogą wydarzyć. Więc nie pitol, że zawsze ma być 100% bo tak nigdy nie będzie. Ten 1% to są przypadki, których jeszcze nie ma ale mogą się pojawić. Przykład - niech wejdzie ustawa, która wymusza na fakturze wydrukowanie nr buta najstarszego (jeśli jest kilku) właściciela firmy. Masz bazę przygotowaną na tą ewentualność? Nierealne? Może i nie, ale nie powiesz, że nigdy tego nie będzie.

abrakadaber napisał(a):

3. int(11) zajmuje 11 bajtów i można tam wpisać max 2^11. Natomiast char(11) także zajmuje 11 bajtów ale można tam wpisać tylko 11 znaków

Jesteś tego absolutnie pewien?
11 bajtów i 11 bitów?

Ani to BCD, ani int.

abrakadaber napisał(a):

aż się prosi aby Ci odpisać, że jak chcesz mieć małą bazę to zabroń userom zapisywać czegokolwiek do niej...

Rozmawiajmy na poziomie.

abrakadaber napisał(a):

A od takich sytuacji jest serwer zapasowy, który może być słabszy, mieć gorsze wyniki ale działający. Pewnie, że to są koszty ale albo ponosimy koszty na dodatkowe zabezpieczenia albo tracimy w razie przestoju.

Jak sobie poradzisz z awarią nie sprzętową, a software'ową, np. z włamaniem, które uszkodziło dane na obu serwerach? Jeśli twierdzisz, że dwa serwery wszystko rozwiążą, to po co robić backupy? Przecież z secondary serwera można odzyskać w tle całą bazę na primary serwer (wybacz język w stylu Dżoany Krupy).

abrakadaber napisał(a):

Coś za coś. BTW ciekaw jestem jak się ma to twoje jak najmniejsza do szybka bo często robi się tak, że kosztem redundancji danych osiąga się większą szybkość zapytań. Tu jak widać też albo jedno albo drugie. To tak jak z tym przysłowiowym mechanikiem: "do wyboru masz: szybko, tanio, dobrze. Wybierz tylko dwie".

Zauważ, że wymieniłem trzy pożądane przeze mnie cechy bazy danych i rozmiar był celowo na trzecim, najmniej istotnym miejscu. Celowo użyłem sformułowania "jak najmniejsza", a nie "mała". Jak już sobie po mnie jeździsz, to chociaż czytaj ze zrozumieniem :-)

abrakadaber napisał(a):

Jeszcze się nie spotkałem z systemem, który w momencie projektowania przewidział wszelkie przypadki jakie się mogą wydarzyć. Więc nie pitol, że zawsze ma być 100% bo tak nigdy nie będzie. Ten 1% to są przypadki, których jeszcze nie ma ale mogą się pojawić. Przykład - niech wejdzie ustawa, która wymusza na fakturze wydrukowanie nr buta najstarszego (jeśli jest kilku) właściciela firmy. Masz bazę przygotowaną na tą ewentualność? Nierealne? Może i nie, ale nie powiesz, że nigdy tego nie będzie.

To się chyba nie zrozumieliśmy. Napisałeś wcześniej "zapisać i jednocześnie odczytać wszystkie (no może jakieś 99%) te dane, dla których została stworzona". Czym innym jest dodanie pola do tabeli, a czym innym 99% pewności, że te dane dadzą się zapisać/odczytać.

abrakadaber napisał(a):

nie pitol

Trochę kultury.

1. nie da się zabezpieczyć niczego na 100% - jak Ci szczury kable przegryzą to rozmiar bazy nie będzie miał znaczenia.
2. czepiłeś się tego rozmiaru bazy jakby to chodziło o różnicę rzędu 100% czy 1000%. Niby z jednej strony wiesz o co chodzi a z drugiej udajesz głupiego. Rozmowa była czy dodać pole is_admin, które będzie zajmować miejsce czy osobną tabelę na adminów. Jak chcesz rozmawiać na poziomie to nie ma sprawy ale sam też prowadź taką rozmowę.
3. nigdzie nie napisałeś, że w jakikolwiek sposób stopniujesze pożądane przez Ciebie cechy bazy. Z drugiej strony to chyba normalne, że zamiast stworzyć w bazie jedną tabelę, która będzie miała, nie wiem, 1000 kolumn typu varchar(20000) i wszystkie możliwe dane w niej trzymać to się robi setki mniejszych i większych tabel. W końcu na tym to polega ale tutaj trzeba to robić z głową i czasami (często) świadomie łamiąc zasady i robiąc "wbrew sztuce" bo np. ważniejsza jest wydajność niż wielkość.
4. napisałem wcześniej "przede wszystkim trzymać dane bez zagrożenia, że zostaną utracone" a potem "być tak zorganizowana aby dało się w niej zapisać i jednocześnie odczytać wszystkie (no może jakieś 99%) te dane, dla których została stworzona". Nie wiem gdzie się tam doszukałeś, że może być tak, że 1% danych "wyparuje" albo nie wiem co. Tutaj chodzi o to, że w momencie projektowania nie jesteśmy w stanie przewidzieć wszystkich przypadków. Ba nawet taki szczegół jak założenie indeksu unikalnego na pole PESEL powoduje, że pewien procent społeczeństwa nie będzie mógł się znaleźć w takiej bazie (a niby miało być tak pięknie).

BTW Twoje stwierdzenie "Powodzenia przy robieniu pełnego backupu dużej bazy" od razu stawia Cię na pozycji super hiper wszystko wiedzącego a mi pokazuje, gdzie moje miejsce. To jeśli mówimy o kulturze. A co do reszty to albo nie przeczytałeś całości albo postanowiłeś pokazać jak to nie mam racji - Twoja sprawa...