Biblioteka na książki

Cześć,

Mam pytanie o schemat bazy danych.
Chodzi o zrobienie prostej biblioteki do rezerwacji wypożyczeń i zwrotów.
Zastanawiałem się jak stworzyć odpowiedni model i w końcu coś takiego urodziłem.
Zależy mi żeby w prosty sposób pobierać dane książek z bazy wraz z ich informacją o dostępności, tzn że jeśli jest zarezerwowana albo wypożyczona to żeby na liście wyszukanych książek żeby też się znalazła.
Dodałem drugą referencję pomiędzy book i transaction żeby własnie trzymać aktualny stan, ale nie wiem czy to jest dobre rozwiązanie bo trzeba pamiętać o aktualizacji tej kolumny w bazie.
Może ta oznaczona na zielona referencja powinna być usunięta a selecta pisać z jakimś grupowaniem po book_id ?

Klucz obcy do tabeli 'transaction' w relacji 'book' jest błędny. Jeżeli chcesz to dorób jakąś tabelę ze stanem magazynowym/bibliotecznym, w którym będzie id książki i jej ilość. Biblioteka może posiadać wiele egzemplarzy tych samych książek przecież. Relacja wiele do wielu pomiędzy autorami i książkami jest prawidłowa.

Jeżeli w tabeli transaction chcesz przetrzymywać informacje o wypożyczonych książkach to ja bym to zrobił tylko w oparciu o pole DateTo ponieważ jeśli user wypożycza książkę (fakt w niektórych bibliotekach jest od razu informacja kiedy należy zwrócić książkę) to nie wiadomo kiedy ją zwróci. Ja bym zatem zrobił to tak, że wypożyczając książkę robimy insert do transaction z DateTo pustym. W momencie gdy ktoś zwraca książkę uzupełniamy pole DateTo i jeśli Ci zależy na wydajności to od razu można by rekord przenieść do archiwum. Co do samego sprawdzenia czy książka jest wypożyczona to zwykły join wystarczy jednak gdybym ja to robił to bym sobie wypożyczanie/zwracanie zrobił na procedurze i w niej ustawiałbym również jakąś flagę na samej książce aby nie musieć niepotrzebnych joinów robić.

Uwzględniłem wasze sugestie, czy może macie jeszcze jakieś żeby usprawnić ten model ?

Po co Ci tabela book_item? W transaction dodaj ID_BOOK z FK do book i po problemie ta tabela book_item jest ci do niczego nie potrzebna

woolfik napisał(a):

Po co Ci tabela book_item? W transaction dodaj ID_BOOK z FK do book i po problemie ta tabela book_item jest ci do niczego nie potrzebna

Ale jeśli tak zrobię to książkę "Krzyżacy" której będzie 30 w bibliotece muszę posiadać 30 rekordów w tablicy "book".

Ale po co? Chyba dalej nie rozumiesz relacji w bazie danych więc postaram się opisać krok po kroku
Tabela book, ma pole ID, Title i jak potrzebujesz to BookCount (czyli Twoje 30 egzemplarzy). Teraz w momencie wypożyczenia robisz insert do tabeli transaction z ID usera, ID książki, DateFrom = SYSDATE i co tam chcesz w transaction_type (bo rozumiem, że np 1 to wypożyczenie, 2 to renowacja, 3 to coś tam jeszcze itd.). Teraz chcąc zliczyć ilość książek na stanie (te które możesz jeszcze wypożyczyć) możesz w prosty sposób zliczać na bieżąco (zliczając ilość rekordów z DateFrom not null i DateTo null dla konkretnego BOOK_ID) lub dodać sobie dodatkowe pole w tabeli BOOK o nazwie availableCount (int) (lub też dodatkowa tabela z ID_BOOK oraz polem availableCount - wszystko zależy od potrzeb), które będziesz zwiększał/zmniejszał przy robieniu insert/update na tabeli transaction.
Podałem dwa rozwiązania bo w zależności od tego czy będziesz transaction archiwizował czy nie to tabela będzie puchła i za jakiś czas rozwiązanie nr 1 może się okazać mało wydajne. Teraz w momencie gdy ktoś książkę zwróci to szukasz w tabeli transaction rekordu dla ID_Usera i ID_Book oraz DateTo = null i aktualizujesz date_to. W momencie gdy archwizujesz rekordy to usuwasz z transaction i robisz insert do transaction_arch lub w przypadku drugiego rozwiązania robisz update na book.availableCount.

@woolfik, a numery egzemplarzy książek w bibliotece będziesz przechowywał w jakiejś kolumnie w tabeli book w postaci tekstu oddzielonego przecinkami czy XMLa?
book_item jak najbardziej ma sens, tylko trzeba tę relację uzupełnić o dodatkowe atrybuty, właśnie numer ewidencyjny, datę zakupu, może jakiś stan zużycia, itd.

Oba rozwiązania mają swoje plusy minusy.
W przypadku rozróżniania, każdego egzemplarza mamy możliwość przechowywania dodatkowych danych dla konkretnego egzemplarza. Ale z kolei podczas rezerwacji nie ma potrzeby rezerwowania jakiegoś konkretnego egzemplarza, a po prostu rezerwuje się książkę o tytule np "Krzyżacy"
Ja chyba bardziej skłaniam się do wersji z ostatniego diagramu czyli z rozróżnieniem każdego egzemplarza.
Choć jeszcze trochę zastanawiam się nad wydajnością zapytania. Bo jak tablica transakcji po paru miesiącach się rozrośnie to może, należałoby ją jakoś archiwizować.

Tak wyglą wybranie wszytskich dostepnych książek, czyli tych które zostały zwrócone lub których

SELECT book.title, book_item.id FROM book_item
inner join book on book_item.book_id = book.id
left outer join transaction on transaction.book_item_id = book_item.id
WHERE (transaction.dateTo is not null AND transaction.dateTo < sysdate()) 
	or book_item.id not in (select book_item_id from transaction)
group by book.title

Tak i nie wszystko zależy od tego jak będziesz dalej się tym posługiwał. Utworzenie odpowiednich HINTów czy Indeksów załatwi temat wydajności i dostępu do danych. Widziałem systemy gdzie jedna tabela zawierała ponad 400 kolumn i prawie mld rekordów jednak dobrze sparametryzowane zapytania i odpowiednio ustawione hinty oraz indeksy powodowały, nie było żadnego problemu z wydajnością. Wszystko zależy od potrzeb.