Mała biblioteka -czy dobrze?

Hej, muszę nauczyć się trochę sql i robie sobie małą bibliotekę z książkami. Na wzgląd tego, że jedna książka może mieć wielu autorów oraz różnie może być z tymi wypożyczeniami zrobiłam sobie taką bazę jak poniżej. Mam kilka pytań:

1. Czy ten podział jest dobry?
2. Nazwy tabel lepsze są w liczbie mnogiej czy pojedynczej?
3. Chcąc otrzymać autorów książek, które obecnie wypożyczył sobie dany czytelnik muszę stosować aż takie zapytanie SQL:

SELECT r.name, a.name, a.surname
	FROM reader r, reader_book rb, book b, book_author ba, author a
	WHERE r.name = 'Marek' AND r.id = rb.idReader AND rb.idBook = b.id AND b.id = ba.idBook AND ba.idAuthor = a.id;

i to z tego względu mam wątpliwości czy jest okey a może to moje zapytanie można usprawnić?

dalsze wytłumaczenie odnośnie zapytania z pkt 3: http://oi59.tinypic.com/1zef9du.jpg

karolinaa napisał(a):

1. Czy ten podział jest dobry?

Wyglada ok.

karolinaa napisał(a):

1. Nazwy tabel lepsze są w liczbie mnogiej czy pojedynczej?

Trudno powiedziec. Ja wole liczby mnogie.

karolinaa napisał(a):

1. Chcąc otrzymać autorów książek, które obecnie wypożyczył sobie dany czytelnik muszę stosować aż takie zapytanie SQL:

SELECT r.name, a.name, a.surname
	FROM reader r, reader_book rb, book b, book_author ba, author a
	WHERE r.name = 'Marek' AND r.id = rb.idReader AND rb.idBook = b.id AND b.id = ba.idBook AND ba.idAuthor = a.id;

i to z tego względu mam wątpliwości czy jest okey a może to moje zapytanie można usprawnić?

Latwiej sie na takie pytania odpowiada jak ktos umiesci skrypt, ktory:

1. Utworzy tabele
2. Wypelni baze przykladowymi danymi.

Wstepnie podpowiem, ze ja bym kombinowal z uzywaniem joinow ale jakbys podala linka do wyeksportowaniej bazy danych to chetniej podalbym przykladowe zapytanie.

1. Liczba pojedyncza. Jeden wiersz przechowuje dane o jednym pracowniku

2. W tym przypadku nie ma najmniejszej potrzeby dodawania skryptow bo model jest jasny.
   Zrob to na joinach. Takie graficzne reprezentacje bardzo ułatwiają zrozumienie idei: http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/
   from reader r join reader_book rb on r.id=rb.idReader join book b on b.id=rb.idBook

i tak dalej.

Ldr napisał(a):

1. Liczba pojedyncza. Jeden wiersz przechowuje dane o jednym pracowniku

Tabela ma wiele wierszy, więc liczba mnoga. :P

@karolinaa warto nabrać nawyku i używać where do określania warunków a joindo łączenia tabel.

fourfour napisał(a):

Ldr napisał(a):

1. Liczba pojedyncza. Jeden wiersz przechowuje dane o jednym pracowniku

Tabela ma wiele wierszy, więc liczba mnoga. :P

@karolinaa warto nabrać nawyku i używać where do określania warunków a joindo łączenia tabel.

To oczywiscie tylko kwestia przyjętek konwencji, niemniej dla mnie uzywanie liczby pojedynczej jest bardzo naturalne.
Podobnie jak w przypadku nazywania kolumny PK, za bardzo niewygodne uwazam nazywanie jej po prostu ID zamiast ID_book na przyklad. Pozniej to bywa mylące przy skomplikowanych joinach.

Ldr napisał(a):

1. Liczba pojedyncza. Jeden wiersz przechowuje dane o jednym pracowniku

No ale przeciez tabela nie sluzy do tego, zeby przechowywac jeden wierszy tylko do tego zeby przechowywac ich wiele (co nie znaczy, ze nie da sie przechowac jednego wiersza ale to dosc rzadkie zjawisko).

Nie twierdze przy tym, ze nie istnieja argumenty przemawiajace na korzysc liczby pojedynczej, ale uzasadnienie ktore podales jest bardziej uzasadnieniem dla pojedycznej nazwy encji a nie pojedynczej nazwy tabeli.

Ldr napisał(a):

1. W tym przypadku nie ma najmniejszej potrzeby dodawania skryptow bo model jest jasny.
   Zrob to na joinach. Takie graficzne reprezentacje bardzo ułatwiają zrozumienie idei: http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/
   from reader r join reader_book rb on r.id=rb.idReader join book b on b.id=rb.idBook

i tak dalej.

Tu nie chodzi o to, ze model jest niejasny. Chodzi o to, ze latwiej sie podaje przykladowe zapytania w sytuacji kiedy mozesz spradzic efekt ich dzialania. Mnie sie czasem zdarza, ze napisze jakies zapytanie i nie do konca dziala one jak nalezy - czasami rekordy sie zdubluja a nie powinny, czasami zapomni sie o grupowaniu niektorych elementow, czasami cos jeszcze. Takie rzeczy latwo sie poprawia jak ma sie mozliwosc pracy na bazie danych. No ale moze to kwestia wprawy.

Ldr napisał(a):

Podobnie jak w przypadku nazywania kolumny PK, za bardzo niewygodne uwazam nazywanie jej po prostu ID zamiast ID_book na przyklad. Pozniej to bywa mylące przy skomplikowanych joinach.

Ja mam nawyk pisania w zapytania nazwa_tabeli.pole i wtedy widzę czy to book.ID czy author.ID i jest mi obojętne, czy w nazwa pola to ID i jakiś opis tabeli. Być może to zły nawyk :)

mam jeszcze jedno pytanie odnośnie unique key. Czym to się różni od unique index i kiedy jest sens tego stosować? Dla kolumy street_name w tabeli addresses dobrze myślę?

http://stackoverflow.com/questions/3794607/unique-index-or-unique-key Ogólnie w dokumentacji takie reczy są zazwyczaj dokłądnie wyjaśnione.

A co do drugiego pytania, nie do końca. Unikalność jest gwarantowana w skali tabeli, jeśli byś założyła constraint na street_name to by wyszło, że dwie osoby nie mogą mieszkać na świerkowej. Unikalność warto wymuszać na przykłąd w przypadku gdy rejestrujesz konsumentów, ktorzy mają się logować do serwisu. Wtedy warto, żeby login był unikalny w skali całej bazy.

fourfour napisał(a):

Ldr napisał(a):

Podobnie jak w przypadku nazywania kolumny PK, za bardzo niewygodne uwazam nazywanie jej po prostu ID zamiast ID_book na przyklad. Pozniej to bywa mylące przy skomplikowanych joinach.

Ja mam nawyk pisania w zapytania nazwa_tabeli.pole i wtedy widzę czy to book.ID czy author.ID i jest mi obojętne, czy w nazwa pola to ID i jakiś opis tabeli. Być może to zły nawyk :)

To nie jest zły nawyk :)

tabele nazywaj w liczbie mnogiej. Users, Books, ..itd.

W przyszłości, gdy zaczniesz używać narzędzi do tworzenia modeli logicznych i zastosujesz generator, który model abstrakcyjny przekłada na model fizyczny konkretnej bazy danych, to nie nastąpi zaskoczenie, że obiekty te są w liczbie mnogiej.
Ponadto wiele programów (np Ruby on Rails) w których (za pomocą generatorów) tworzysz obiekty bazodanowe, też mają "manierę" ;-) by wygenerować w bazie tabele z nazwami w liczbie mnogiej.

Jeszcze taka mała dygresja, bo nie wiem, czy mam mieszać Ci w głowie ;-) (czytaj: czy masz swój model przebudować)
Zwróć uwagę, że w prawdziwej bibliotece, każda książka ma ileś egzemplarzy i wypożyczamy konkretny egzemplarz książki. Ten model zakłada, że każdy tytuł jakiejś pozycji wydawniczej występuje w bibliotece tylko jeden raz.

zmagam się teraz od 4h z takim problemem:
Mam studenta z group_id , ale nowo dodany student do tabeli nie musi od razu mieć przydzielonej grupy - korzystam, więc przy group_id z null default null. Jeżeli zaś z tabeli z grupami usunę daną grupę to chcę aby wszystkim studentom, którzy mieli group_id "zreferowany" do tej grupy aby pojawiło im się NULL, a w przypadku modyfikacji aby pole po prostu się uaktualniło. czyli w moim przypadku to będzie opcja on delete set default on update cascade lub on delete set null on update cascade (defaultowo przy group_id dałam null więc wyjdzie na to samo. i treaz tak:

/* tworze tabele grupy */
create table if not exists `groups` (
    /* klucz podstawowy */
    `group_id` int(11) unsigned not null auto_increment primary key,
    `name` varchar(30) not null
) ENGINE = InnoDB;

/* tworze tabele studenci */
create table if not exists `students` (
    /* klucz podstawowy */
    `student_id` int(11) unsigned not null auto_increment primary key,
    `name` varchar(30) not null,
    `surname` varchar(30) not null,
    /* wiek z mozliwym nullem, bo nie bedzie to od razu wymagane */
    `age` tinyint null default null,
    /* niektorzy studenci nie zostana od razu przydzieleni do odpowiedniej grupy */
    `group_id` int(11) unsigned null default null,
    /* tworze index na polu group_id */
    index `FK_GROUP` (`group_id`),
    /* klucz obcy do groupy */
    constraint `FK_GROUP` foreign key (`group_id`) references `groups` (`group_id`)
    	on delete set default on update cascade
    ) ENGINE = InnoDB;

i niestety po wysłaniu zapytania otrzymuję:
#1005 - Can't create table 'hibernate_przyklad.students' (errno: 150)

jakie są prawidłowe metody tworzenia relacji w takim przypadku jaki opisuję? dodam, że zapytanie zadziała w przypadku opcji on delete cascade on update cascade.