Baza danych Szkoły Językowej - w jaki sposób połączyć tabele?

pytanie zadane 11 grudnia 2015 w SQL, bazy danych przez ScriptyChris Mędrzec (190,190 p.)

Z góry informuję, że nie proszę o gotowca, tylko o wyjaśnienie w jaki sposób połączyć ze sobą poszczególne tabele (czy też rekordy). Z SQL jestem praktycznie zielony, oprócz podstawowej znajomości SELECT.

Muszę utworzyć bazę danych dla szkoły językowej oferującej kursy w kilku językach i poziomach:

BAZA DANYCH DLA SZKOŁY JĘZYKOWEJ:
Zasięg: ogólnopolski
Języki: angielski, niemiecki
Podział na poziomy trudności: podstawowy, średnio-zaawansowany, zaawansowany
Obszary tematyczne: medycyna, prawo, biznes, IT, mechanika...
Kursy do certyfikatów oraz egzaminy certyfikujące

DZIAŁANIA NA BAZIE:
Uzyskanie informacji o ofertach szkoły wg:
•   lokalizacji (miasto, placówka)
•   oferowany język
•   kategoria (kurs, egzamin)
•   stopień zaawansowania
•   obszar tematyczny
•   liczba godzin szkolenia
•   cena

Zapisy (wraz z opłaceniem dla różnych kanałów płatności) na:
•   kurs językowy
•   kurs przygotowawczy do egzaminu
•   egzamin

Sprzedaż produktów wspomagających naukę języków:
•   książki
•   podręczniki
•   arkusze ćwiczeń
•   płyty CD/DVD

Ocenianie przez klientów usług świadczonych przez szkołę (wg kryteriów):
•   obsługa administracyjna
•   trener
•   warunki lokalowe
•   dodatkowe udogodnienia

Proszę o wskazówki co z czym połączyć.Utworzyłem tabele, ale nie wiem w jaki sposób je połączyć (gdzie utworzyć jakie klucze - choć przy "id" dałem podstawowe). Zapewne brakuje jeszcze jakichś rekordów (np. przypisanie ucznia do kursu), ale nie wiem jak się dalej za to zabrać.

http://imgur.com/4Ekx2yp (lepsza jakość screena)

2 odpowiedzi

odpowiedź 12 grudnia 2015 przez mowmiheniek Stary wyjadacz (11,900 p.)
wybrane 18 grudnia 2015 przez ScriptyChris

Najlepsza

Najlepiej sobie to odwzorować tak jak w życiu to wygląda.

Do tych tabel co zrobiłeś:

Szkoła językowa może mieć wiele placówek ale zakładamy, że w jednej placówce jest tylko jedna szkoła językowa. To będzie połączenie jeden do wielu. Zatem dodajesz do tabeli palcówki kolumnę ID-szkoła-jezykowa i łączysz z ID tabeli szkoła językowa relacją jeden do wielu (gdzie jeden to ID ze szkoły językowej).

Wiele tenerów pracuje w jednej placówce to robisz tak samo. Dodajesz do tabeli trenerzy kolumnę ID-placówka i łączysz z tabelą placówki z ID-placówka.

Co innego gdy trenerzy się przemieszczają i pracują w rożnych placówkach. Wtedy pasuje zrobić dodatkową tabelę, która połączy ID trenera i ID placówki.

Tu masz do poczytania:

http://www.teacher.webd.pl/kurs_mysql/k_2_2_3.php

Zauważ, że w tabelach: placówki, trenerzy, kursanci masz powtarzające się dane. To niedobrze. Nadmiarowość jest niepożądana. Usuń miasto i zrób osobną tabelę miasta z ID-miasta i nazwa miasta. (tz. tabela słownikowa).

Z kodem i ulicą zrobiłbym to samo bo wygląda mi, że będą się powtarzać (zwłaszcza w małych miejscowościach).

odpowiedź 13 grudnia 2015 przez ScriptyChris Mędrzec (190,190 p.)
edycja 14 grudnia 2015 przez ScriptyChris

@mowmiheniek, dzięki za wskazówki.

Dodałem kilka tabel, ale nie będę bawił się w redukcję nadmiarowości, o której wspomniał mowmiheniek - baza ma po prostu działać i tyle.

Tak struktura i relacje wyglądają teraz tak: http://imgur.com/1HRxCW2

Mam kilka pytań:

1. Czy wygląda to prawidłowo (tak jak jest opisane w pierwszym poście)?

2. Która metoda wiązań jest prawidłowa, przy kursach, czy placówkach? Przy kursach korzystałem z dodawania wewnętrznych relacji (w MySQL tak to się nazywa), a przy placówkach użyłem "foreign key constraints". Nie wiem jaka jest różnica, dlatego pytam która wersja jest prawidłowa (w moim przypadku).

3. W jaki sposób dodać:

a) Ocenianie przez klientów usług świadczonych przez szkołę (wg kryteriów):
•   obsługa administracyjna
•   trener
•   warunki lokalowe
•   dodatkowe udogodnienia

b) pozostałe akcje na bazie opisane w pierwszym poście (zapisy do kursów, sprzedaż produktów, uzyskiwanie informacji o kursach danej szkoły wg różnych kryteriów)?

komentarz 15 grudnia 2015 przez mowmiheniek Stary wyjadacz (11,900 p.)

Zdecydowanie tabela kursy wygląda na prawidłową, oprócz relacji id-kurs do id-placowka. Musisz zrobić tak samo z placówkami.

Oceny - to nowa tabela z relacją do id-kursant, z kolumnami jak w punkcie a.

Zapisy - nowa tabela, gdzie są kolumny: id-platnosci i jest powiązane z kolumną platnosci, w której trzymasz rodzaje płatności. Następna kolumna id-kursu powiązane z kolumna id-kurs tabeli kursy.

Kurs przygotowawczy do egzaminu - nie wiem co on będzie zawierał?

Egzamin - tez nie wiem, ale może to być kolumna która przyjmuje wartość 0 lub 1 (był lub nie), ale również może być też ocena z egzaminu.

Najlepiej ty będziesz wiedział co kolumny zawierają. Ja ci tylko zaproponowałem jak to może wygląda.

komentarz 15 grudnia 2015 przez ScriptyChris Mędrzec (190,190 p.)

Wielkie dzięki za kolejne wskazówki:)

Kurs przygotowawczy do egzaminu - nie wiem co on będzie zawierał?

Kurs przygotowawczy do egzaminu, to po prostu typ/rodzaj kursu, któy przygotowuje do danego egzaminu z certyfikatem - tam ma być wartość "true" (że kurs przygotowuje do egzaminu) oraz podany typ certyfikatu, jaki można będzie uzyskać po zdaniu egzaminu. Jednakże uczeń odbywający kurs przygotowawczy do egzaminu nie musi konieczne do niego potem przystępować - chcę aby tabela z egzaminami oraz przygotowaniem do egzaminu byłą niezależna (można uczęszczać na kurs przygotowawczy, ale nie trzeba podchodzić do egzaminu i w drugą stronę - można podejść do egzaminu bez uczęszczania na kurs). Jak zrobić takie relacje?

Jeszcze jedna rzecz. W jaki sposób ułożyć sprzedaż produktów (książek, podręczników, płyt)?

komentarz 15 grudnia 2015 przez mowmiheniek Stary wyjadacz (11,900 p.)

Ale dlaczego nie w jednej tabeli?

Tabela egzamin. Kolumny: kurs-przygotowawczy - może być true lub false, egzamin-koncowy - też true lub false i certyfikat - tutaj może być połączenie z tabelą z certyfikatami.

Tabela Sprzedaż produktów. Może być połączona zarówno z kursantem jak i placówką (jeżeli placówka sprzedaje produkty), może także być połączone z tabelą płatności.

•   książki
•   podręczniki
•   arkusze ćwiczeń
•   płyty CD/DVD

Z tych punktów zrobiłbym nowe tabele np. ksiazki z kolumnami ID, autor, tytuł, cena. Tabela książki połączona ma relacje ze sprzedażą, a także może mieć z z tabelą języki i tabelą poziomy.

Do pozostałych punktów podobnie.

komentarz 16 grudnia 2015 przez ScriptyChris Mędrzec (190,190 p.)
edycja 16 grudnia 2015 przez ScriptyChris

Zrobiłem. Teraz całość wygląda tak: http://imgur.com/enE4Eht

Czy już jest prawidłowo?

Pytanie: dlaczego, pomimo tego, że w niektórych kolumnach ustawiłem "boolean", to potem pojawia się i tak "tinyInt"? Za każdym razem, gdy przestawiam na boolean, to i tak powraca tinyInt. A ja chcę, aby akurat wybrane kolumny były true/false, gdyż wtedy będzie możliwość wyboru, czy dana kolumna ma być wprowadzona/użyta/aktywowana/widoczna/wdrożona (nie wiem jak to w MySQL się nazywa), czy nie.

komentarz 17 grudnia 2015 przez mowmiheniek Stary wyjadacz (11,900 p.)

Relacje: Sprzedaz - Produty są złe.

Jeżeli wszystkie produkty zawierają to same kolumny (autor, data wydania, cena itp.). To w tabeli Sprzedaż dałbym id-produktu które ma relacje z id-produktu w tabeli Produkty.

Do tabeli Produkty dodałbym id-kategoria, która ma relacje z nową tabelą Kategorie. Tabela Kategoria będzie zawierała id-kategoria i kategoria (wpisy w rekordzie będą: książka, podręcznik, CD), tylko nie z wartościami int ale z varchar.

Do tabeli Sprzedaz dodałbym id-kursant i połączył z tabelą Kursanci (wiedziałbyś komu sprzedajesz).

Do Sprzedaży możesz dodać też rodzaj płatności podobnie jak tabelą Kategoria.

Co do boolean - w MySQL słowa BIT, BOOL i TINYINT to synonimy. Dzieje się tak bo MySQL nie obsługuje true i false. Zamiast tego używa 0 i 1. Użyj BIT.

komentarz 18 grudnia 2015 przez ScriptyChris Mędrzec (190,190 p.)

Jeżeli wszystkie produkty zawierają to same kolumny (autor, data wydania, cena itp.). To w tabeli Sprzedaż dałbym id-produktu które ma relacje z id-produktu w tabeli Produkty.

Ja to wymyśliłem tak, że w tabeli sprzedaż są nazwy produktów jako bool i w zależności co zostanie wybrane, to z tabeli produkty wpisuje się autor,dane,tytuł itd. (do każdego produktu z tabeli sprzedaż de facto pasuje każda wartość (czy też rekord) z tabeli produkty. Bo zarówno podręcznik, jak i płyta mają jakiegoś autora (czy wydawce), tytuł itd.

Do tabeli Produkty dodałbym id-kategoria, która ma relacje z nową tabelą Kategorie. Tabela Kategoria będzie zawierała id-kategoria i kategoria (wpisy w rekordzie będą: książka, podręcznik, CD), tylko nie z wartościami int ale z varchar.

Dzięki, ale już i tak baza jest rozrośnięta :)

Do tabeli Sprzedaz dodałbym id-kursant i połączył z tabelą Kursanci (wiedziałbyś komu sprzedajesz).

Ja pomyślałem, że jeśli w placówce można dokonać zakupy, to może to zrobić każdy (nie ważne, czy jest kursantem) "z ulicy", więc w tabeli sprzedaż nie muszę dodawać id-kursanta. Choć trafne spostrzeżenie :)

---------------------------------------------------------------

Pokazałem strukturę bazy prowadzącemu i ogólnie jest ok, z tym że mam zmienić w tabelach: certyfikaty, tematyka, języki, poziomy nazwy kolumn na nazwa-[tu wstawić nazwę tabeli] i dopiero w tabeli jako rekordy podać, to co było do tej pory. // To już zrobiłem.

Druga rzecz, której akurat nie rozumiem. Prowadzący kazał usunąć tabelę dane (kolumnę nr-konta-bankowego przenieść do miasta lub placówki) i tabelę placówki jakoś rozdzielić. Mają w niej zostać kolumny: kod-pocztowy, miasto, ulica, numer - a resztę (id-sprzedaz, id-platnosc, id-egzamin, id-kurs) gdzieś przenieść i połączyć taką tabelę placówki z tabelą miasta poprzez nazwa-miasta. Ja tego nie rozumiem. Ja to pomyślałem tak, że ogólnie ta szkoła językowa (jako firma) jest obecna w kilku miastach, a tych może być po kilka placówek - dlatego mam tabelę miasta i z niej odchodzi tabela placówki, gdzie w każdej jest inny adres i jej dane.

Wydaje mnie się, że chodzi o to, aby w tabeli miasta dać nazwa-lokalizacji i od niej dać nową tabelę lokalizacje. Tam zawrzeć dane adresowe każdej z placówek i dopiero od tabeli lokalizacje dać odnośnik (znaczy relację) do tabeli placówki, gdzie już tylko informacje o kursach, sprzedaży itd by były. Co o tym myślisz?

http://imgur.com/vQl3zwK

P.S. Czy kolejność kolumn w tabeli ma znaczenie? Bo żeby poprawić czytelność wykresu przestawiłbym w niektórych tabelach kolumny, aby relacje były lepiej widoczne.

[edit]

Zapomniałem przed zrobieniem screena usunąć z tabeli placówki: kod-pocztowy, miasto, ulica, numer - ale docelowo usunąłem.

komentarz 19 grudnia 2015 przez mowmiheniek Stary wyjadacz (11,900 p.)

Tak na szybkiego bo już późno. Resztę napiszę jak będę miał więcej czasu.

Ja to wymyśliłem tak, że w tabeli sprzedaż są nazwy produktów jako bool i w zależności co zostanie wybrane, to z tabeli produkty wpisuje się autor,dane,tytuł itd. (do każdego produktu z tabeli sprzedaż de facto pasuje każda wartość (czy też rekord)

To tak nie działa. Powiedzmy, że ktoś kupuje książkę. W tabeli sprzedaż będzie w pozycji książka 1 a w reszcie 0. Masz 5 różnych książek, skąd tabela Produkty ma wiedzieć która to książka? Dodaj do tabeli Produkty kategoria (a w niej będziesz miał wpisy czy to książka, podręcznik czy płyta CD).

Błędna jest taka relacja gdzie jedna strona przyjmuje bit a druga int. Typy danych mają być takie same.

Tabela miasta: wywal id-lokalizacja. A w tabeli lokalizacje zamień id-lokalizacja na id-miasto i zrób relacje z tabelą miasta. Za dużo identyfikatorów w tabeli miasta, jeden wystarczy.

Ja pomyślałem, że jeśli w placówce można dokonać zakupy, to może to zrobić każdy (nie ważne, czy jest kursantem) "z ulicy", więc w tabeli sprzedaż nie muszę dodawać id-kursanta.

Możesz zawsze do bazy dodać kursanta z ID np. 666, o imieniu nieznajomy. Wtedy sprzedający wybiera tą osobę i wiesz że to nie kursant ;)

mam zmienić w tabelach: certyfikaty, tematyka, języki, poziomy nazwy kolumn na nazwa-

To już kosmetyka.

Prowadzący kazał usunąć tabelę dane

To już ci wyjaśnię później.

Na razie.

komentarz 19 grudnia 2015 przez mowmiheniek Stary wyjadacz (11,900 p.)

Tabela Dane jest nie logiczna. Zazwyczaj w jednej tabeli umieszcza się dane dotyczące pewnego działu, zagadnienia, formularza itp. Np. Nie umieszczasz w tabeli osoba - kolor samochodu lub tytuł książki. W tabeli dane masz id-miasta i nr-konta-bankowego - chociaż te dane nie są powiązane bezpośrednio (miasta nie mają kont bankowych -może mieć urząd miasta).

Dlatego przenieś nr konta do placówki.

połączyć taką tabelę placówki z tabelą miasta poprzez nazwa-miasta

Twój nauczyciel założył, że nazwa miasta jest niepowtarzalna. Dlatego można ją traktować jako ID. O ile mi wiadomo nazwy miast się nie powielają, ale nazwa miasta i wsi, wsi i wsi już tak.

Nadal będziesz miał inne adresy placówek (inne ulice, nr) ale będzie to samo miasto.

Tabela miasto będzie tabelą słownikową.

id-sprzedaz, id-platnosc, id-egzamin - to możesz przenieść do tabeli kurs. id-kurs musi zostać bo musisz to jakoś połączyć.

komentarz 19 grudnia 2015 przez ScriptyChris Mędrzec (190,190 p.)
edycja 19 grudnia 2015 przez ScriptyChris

Trochę się pogubiłem. Czy mógłbyś mi na tym screenie choćby w Paincie to narysować :)?

http://imgur.com/vQl3zwK

[edit]

Ok, poradziłem sobie. Dzięki!