Pomoc w projektowaniu struktury

Witam, mam za zadanie przygotować bazę danych apteki, w której mam zawrzeć następujące wytyczne:
[quote]1. Prowadzi ewidencję stanu magazynu
2. Również spis dostawców
3. Realizacja recept wg spisu lekarstw z uwzględnieniem ich odpłatności
4. Ponieważ część lekarstw jest odpłatna nie w 100% - reszta jest
uwzględniana w należnościach od wojewody[/quote]

Na razie jestem na etapie projektowym i nie wiem jakie tabele będą mi potrzebne i jak je połączyć do punktu 3 i 4. Liczę na jakąkolwiek dyskusje i pomysły bo jak na razie to mam jedynie pokreślone kartki i nie wiem jak to zrobić by było najprościej i najbardziej przejrzyście.
Z góry dziękuje za pomoc. :)

Sądzę, że warto abyś zaczął od tego: http://www.sql-kursy.pl/ms-sql-kurs-tworzenie-tabel-modyfikacja-3.html

A tak na serio, pokaż co już wymyśliłeś i wtedy zaczniemy dyskusję.

Tak tez zrobie, nie licze na gotowce, a bardziej na rady. Aktualnie znajduje sie w podrozy i jutro wstawie moja pierwsza koncepcje.

Na tą porę mogę powiedzieć, że utworze tabele leki(id, nazwa) magazyn (id, nazwa, stan_aktualny), klient(k_id, imie, nazwisko), dostawcy(d_id, nazwa), recepta(id, imie, nazwisko, data_wystawienia, czy_zaplacono), nie mam pojecia jak poradzic sobie z tą zniżka/refundacja (naleznosci od wojewody) i jakie tabele moge jeszcze zawrzec w moim projekcie by uzyskac zamierzony rezultat.

Myślałem by jeszcze dac zobowiazania (d_id, nazwa_d, wartosc_z) i naleznosci (k_id, imie, nazwisko, wojewoda) gdzie wartosc_z i wartosc_n o suma zobowiazan/naleznosci, a wojewoda to refundacja (tez wchodzi w sklad tabeli leki)

Refundajce/ceny leków można rozwiązać jak oddzielną tabelę gdzie mamy id, prc_refund_with_recept, prc_refund_without_recept,actual_standard_price

i później w momencie wstawiania zamówienia może być określone czy klient płaci pełną stawke/upust/upust dla recepty

A gdyby zrobić to tak: LEKI(id, nazwa, cena_z_upustem, cena_brutto, cena_netto), MAGAZYN(id, nazwa, ilosc_sprzedanych_zu, ilosc_sprzedanych_bu, ilosc_kupionych, stan_ogolem), KLIENT(k_id, imie, nazwisko), DOSTAWCY(d_id, nazwa_d), RECEPTA(id, nazwa, imie, nazwisko, data_wystawienia, cena_z_upustem, cena_bez_upustu, czy_zaplacono), ZOBOWIAZANIA(d_id, nazwa_d, wartosc_z), NALEZNOSCI(k_id, imie, nazwisko, suma_sprzedanych_zu, suma_sprzedanych_bu, wartosc_ogolem), BILANS (zysk_netto, zysk_brutto)

Jakieś uwagi, rady?

Moim zdaniem takie tabele są niezbyt dobre. Takie coś realizuje się jak klasyczny magazyn i tyle. Czyli masz dokument zakupu (wraz z pozycjami) oraz dokument sprzedaży (też wraz z pozycjami). Oba typy dokumentów umieszczasz w tych samych tabelach. Zaznaczasz sobie tylko czy to jest zakup czy sprzedaż i na tej podstawie liczysz stany magazynowe jednym prostym zapytaniem select. Nie wiem czy to jest projekt na studia czy taki który ma działać realnie, ale jeśli realnie to musisz uwzględnić o wiele więcej rzeczy niż napisałeś w 1 poście.

W każdym razie ja bym dał takie tabele:

• LEKI
• KONTRAHENCI
• KONTRAHENCI_INDYWIDUALNI (można dywagować czy nie połączyć z tabelą KONTRAHENCI)
• DOKUMENTY
• DOKUMENTY_POZYCJE

Pytanie czy do 1 recepty będzie 1 paragon, czy nie? Jeśli będziesz chciał realizować kilka recept na 1 dokumencie sprzedaży to trzeba by jeszcze dodać tabelki:

• RECEPTY
• RECEPTY_POZYCJE
  oraz wypadałoby połączyć RECEPTY_POZYCJE wraz z DOKUMENTY_POZYCJE.

Ogólne zasady podczas projektowania struktury bazy danych.

1. Najpierw zrozum dokładnie problem, który chcesz rozwiązać. Zastanów się jak taka apteka by faktycznie działała - skąd się biorą leki, co je charakteryzuje, skąd się biorą zniżki i co je charakteryzuje. W jaki sposób leki będą "schodziły" ze stanu magazynowego, czym dla apteki może być magazyn. Takie rozważania pozwolą Ci wyobrazić sobie, co tak na prawdę jest istotne i znacznie ułatwią znalezienie rozwiązania.
2. Zaczynaj projektowanie od tych najbardziej oczywistych elementów - zidentyfikuj encje ("obiekty"), których dane będziesz musiał przechowywać oraz ich właściwości (np. właśnie leki, recepty, zamówienia/zakupy, dostawcy). To powinno Ci dać pierwsze tabele w Twojej bazie.
3. Określ, jak te encje ze sobą wchodzą w interakcje - co od czego zależy i w jaki sposób. Np. każdy lek skądś pochodzi - od dostawcy, zatem niemal na pewno będzie potrzebne połączenie hipotetycznej tabeli leki z tabelą dostawcy, albo za każdym razem, gdy realizowana jest recepta refundowana należy odpowiednią kwotę uwzględnić w należnościach od wojewody - to sugeruje z kolei połączenia tabeli recepty z hipotetyczną tabelą wojewoda_naleznosci. Na tym etapie uda Ci się połączyć we właściwy sposób ze sobą podstawowe tabele.
4. Pomału wprowadzaj kolejne "oboczności", czyli wszelkie elementy, które komplikują całość, np. te zniżki - łatwiej Ci je będzie umieścić w już istniejącym uproszczonym schemacie, niż uwzględniać wszystko od początku.
   Polecam też skorzystać z jakiegoś narzędzia, w którym mógłbyś sobie ten schemat pomału tworzyć - kartek nakreślisz strasznie dużo ;)
   Niestety ja pracuję na co dzień z MySQL i nie mogę polecić żadnego narzędzia dla MSSQL, ale podejrzewam, że coś podobnego do MySQL Workbench istnieje.

Najważniejsze to dobre zrozumienie problemu i wiedza o tym, co tak na prawdę masz zrobić.
Myślę też, że warto się skupić na tym, co jest określone w zadaniu - nie ma w nim mowy o konieczności robienia bilansu ile apteka zarobiła (o ile wkleiłeś całe zadanie ;)), zatem nie odpływaj też za daleko z tym projektowaniem, bo gdybyś chciał zrobić kompleksową strukturę dla apteki, to tabel będzie zapewne baaardzo dużo :)

[Adam]

Dziękuje za wszystkie odpowiedzi. Jest to projekt na studia i z tych czterech punktów mam utworzyć 8-10 tabel, stąd ta tabela BILANS by spełnić choć wymagane minimum. Jeżeli chodzi o diagram ERD to mógłbym już taki utworzyć z istniejących tabel, problem jest z utworzeniem tego minimum, a jeżeli chodzi o interakcje to myślałem, że łatwo się domyśleć przez wstawione atrybuty. LEKI DOSTAWCY KLIENCI RECEPTY WOJEWODA_NALEŻNOŚCI to tylko 5 tabel, brakuje jeszcze 3. Mógłbym się zgodzić na wariant Mr.YaHooo, ale brakuje właśnie tych należności, bo rozumiem, że spis dostawców i klientów jest w tabeli KONTRAHENCI. Pozostaje jeszcze kwestia atrybutów do poszczególnych tabel.

Wydaje mi się, że jeśli dobrze zidentyfikujesz relacje i zaczniesz rysować to bez sztucznego mnożenia tabel, powinna ich wyjść odpowiednia ilość. Ponadto na pewno będą potrzebne jakieś tabele słownikowe - np. na typ zniżki, ewentualnie status zamówienia/zakupów, typ płatności. Można by się też zastanowić, czy tabela klienci jest potrzebna - czy jak klient przychodzi do sklepu, to pani zawsze go pyta o imię, nazwisko i pesel? Być może ma to być apteka internetowa, ale wtedy kłopot z realizacją recept. A może uznamy, że prowadzi sprzedaż normalną i internetową - wtedy taka tabela ma sens, ale to powoduje, że przy zamówieniu/zakupach nie zawsze będzie klient. Można też pomyśleć, żeby były 2 tabele - osobna na zakupy "normalne" i osobna na zamówienia internetowe...

Pomyśl jakbyś chciał, żeby ta apteka działała i do tego dostosuj tabele - liczbą się nie martw, bo prawdopodobnie sama wyjdzie prawidłowa.

A' propos tabel słownikowych - tutaj koledzy wyjaśniają co i jak: Zastosowanie tabel słownikowych :)

kchteam napisał(a):

Można by się też zastanowić, czy tabela klienci jest potrzebna - czy jak klient przychodzi do sklepu, to pani zawsze go pyta o imię, nazwisko i pesel? Być może ma to być apteka internetowa, ale wtedy kłopot z realizacją recept. A może uznamy, że prowadzi sprzedaż normalną i internetową - wtedy taka tabela ma sens, ale to powoduje, że przy zamówieniu/zakupach nie zawsze będzie klient. Można też pomyśleć, żeby były 2 tabele - osobna na zakupy "normalne" i osobna na zamówienia internetowe...
Faktycznie, jednak jeśli mamy receptę, to na recepcie widnieje imię oraz nazwisko pacjenta, więc tu problemu nie ma. Natomiast w przypadku gdy nie ma recepty można dopuścić pustego kontrahenta. Bo moim zdaniem mieszanie dostawców z klientami indywidualnymi nie ma sensu. Ta druga tabela będzie bardzo duża po jakimś czasie używania, bo większość ludzi i tak przychodzi raz na kilka lat po receptę. Tak więc nawet można olać i dać do wpisania jako dodatkowe pola w dokumencie sprzedaży. Ale wtedy zmniejszamy ilość tabel.

W moim rozwiązaniu jeszcze doszłaby tabelka słownikowa z typem dokumentu (Faktura zakupu/faktura sprzedaży/Faktura korygująca/paragon/zwrot itp....). Tak samo warto by było do leków dać jakąś grupę. Np zioła/tabletki przeciwbólowe/antybiotyki... Takie rzeczy są w normalnych programach i to jest normalne. Podobnie można zapisywać należności od wojewody do nowej tabeli. Dochodzi nowa tabela. A żeby jeszcze powiększyć liczbę tabel można dać kolejną trzymającą aktualne stany leków. Ale tu można trzymać ze względu na cenę zakupu/serię/datę ważności. Akurat te rzeczy w aptekach są bardzo ważne. Dzięki takiej tabeli każdego rozchodu nie musisz liczyć stanów na bieżąco. Wystarczy trigger na tabeli pozycji dokumentów który uaktualni stany edytowanego leku.

Widze tu bardzo ambitne pomysly za co dziekuje, jednak ten projekt tego nie wymaga, to dopiero poczatki z mssql i to wszystko nie wymaga, az tak głębokiej analizy. Przypuscmy, ze jest to "sklep z lekami", ktory prowadzi spis dostawcow i klientow, recepta łączy sie z znizka (naleznosci od wojewody), a stan zasobow funkcjonuje jak w magazynie. Chodzi bardziej o hurtownie z lekami gdzie wystepuje przy niektorych produktach znizka przy zakupie.

@quinek inaczej nie jestem w stanie tego zrobić na około 10 tabelach. Ale jeśli Ci chodzi o taką hurtownię, to tu jest jeszcze prościej, bo mam pomysł na taką strukturę:

• DOKUMENTY
• DOKUMENTY_POZYCJE
• KONTRAHENCI
• LEKI
• LEKI_ZNIZKI

Czemu oddzielna tabela na zniżki? Bo tak naprawdę zniżki mogą być różne w zależności od daty. Zatem użytkownik może sobie przygotować wartość zniżek tydzień przed ich wejściem w życie. W tabeli DOKUMENTY jeśli mam sprzedaż na podstawie recepty od razu wpisuję dodatkowe dane z recepty i koniec. Wtedy pobieram sobie zniżkę i już.

@Mr.YaHooo a jeżeli by to zrobić nie w zależności od daty tylko od produktu? I jakie przykładowo atrybuty zawarłbyś w tych tabelach? Nie bardzo rozumiem dlaczego dostawcy i klienci są w jednej tabeli KONTRAHENCI. Chciałbym to zrobić tak jak Ty w 5 tabelach ale niestety musze spełnić to minimum 8 tabel.. Nie zależy mi by ten projekt był obfity, tylko by działał sprawnie i był czytelny, i bym przy okazji się czegoś nauczył.

quinek napisał(a):

@Mr.YaHooo a jeżeli by to zrobić nie w zależności od daty tylko od produktu?
To wtedy mamy bardziej prostą sprawę. Jeśli wiadomo, że dany lek podlega refundacji w x% albo nie. To wystarczyło by dodatkowe pole w tabeli LEKI. Jeśli system miałby być bardziej inteligentny i pozwalać na wcześniejsze wprowadzenie od kiedy refunduje się dany lek, albo refundacja może być zmienna, to ja bym dołożył dodatkową tabelę jak wyżej.

quinek napisał(a):

I jakie przykładowo atrybuty zawarłbyś w tych tabelach?

• DOKUMENTY (ID, DATA_DOKUMENTU, TYP_DOKUMENTU_ID, KONTRAHENT_ID, .....)
• DOKUMENTY_POZYCJE (ID, DOKUMENT_ID, LEK_ID, CENA_NETTO, CENA_BRUTTO, ILOSC, ZNAK, REFUNDACJA, WARTOSC_NETTO, WARTOSC_VAT, WARTOSC_BRUTTO, ....)
• KONTRAHENCI (ID, NAZWA, ADRES, NIP, ....)
• LEKI (ID, NAZWA, REFUNDACJA, )

quinek napisał(a):

Nie bardzo rozumiem dlaczego dostawcy i klienci są w jednej tabeli KONTRAHENCI.
Zakładam, że w tabeli KONTRAHENCI dodaję kontrahentów firmowych, czyli u Ciebie to raczej będą dostawcy. Natomiast klienci prywatni, czyli zwykły Kowalski będzie siedział bezpośrednio w dokumencie. Nie ma sensu tworzyć oddzielnej kartoteki, ponieważ i tak w 95% będzie użyty tylko w jednym dokumencie. Jednak jak chcesz to można dołożyć drugą tabelę:

• KONTRAHENCI_INDYWIDUALNI (ID, IMIE, NAZWISKO, ADRES, PESEL).
  Jeśli nie, to te dane równie dobrze mogą być umieszczone na tabeli DOKUMENTY.

quinek napisał(a):

Chciałbym to zrobić tak jak Ty w 5 tabelach ale niestety musze spełnić to minimum 8 tabel.. Nie zależy mi by ten projekt był obfity, tylko by działał sprawnie i był czytelny, i bym przy okazji się czegoś nauczył.
Zapomniałem jeszcze o tabeli:
DOKUMENTY_TYPY (ID, NAZWA, SYMBOL, ZNAK)
Będą się tam znajdować obsługiwane typy dokumentów:
''ID |Nazwa |Symbol |ZNAK
---+--------------------------+-------+-----
1 |Faktura Zakupu |PZ |1
2 |Faktura Zakupu-korekta |PZN |1
3 |Faktura Sprzedazy |FT |-1
4 |Faktura Sprzedazy-Korekta |FTN |-1
5 |Paragon Fiskalny |FTP |-1''
Znak jest po to aby było wiadomo z jakim znakiem należy brać ilość z pozycji podczas liczenia stanów magazynowych. Od taki trick. Wtedy stany można policzyć np. tak:

select 
  LEK_ID,
  sum(ILOSC*ZNAK) as STAN
from
  DOKUMENTY_POZYCJE
group by
  LEK_ID

Dla uproszczenia założyłem sobie, że znak trzymam pole ZNAK w tabeli DOKUMENTY_POZYCJE. Jednak bardziej książkowo byłoby aby to było brane ze złączenia z tabelą DOKUMENTY_TYPY. Wykładowca może się o to czepiać. Bo struktura bazy nie jest w pełni znormalizowana (mamy nadmiarowe dane).

Tym sposobem mamy 6 tabel. Czyli jeszcze jakieś 2 by się przydały. Osobiście dałbym dodatkową tabelę na grupy leków. Dzięki czemu można pokazać np. tylko leki przeciwbólowe czy antybiotyki.

Dodatkowo wypadałoby zrobić jakąś tabelę dla stawek oraz sposobów płatności:
STAWKI_VAT (ID, SYMBOL, STAWKA)
SPOSOBY_PLATNOSIC (ID, SYMBOL, NAZWA)

I chyba mamy tyle tabel ile trzeba minimalnie wydzielając tabelę dla klientów indywidualnych.

@Mr.YaHooo Nie jestem do końca pewny czy taka ma być koncepcja, wstawiam kod w załączniku tworzący tabele, jeżeli widzisz jakiś błąd bardzo bym prosił o wyjaśnienie.

Więc tak:

• w tabeli DOKUMENTY brakuje mi pol SPOSOB_PLATNOSCI_ID
• w tabeli DOKUMENTY przydałoby się jeszcze pole na numer dokumentu.
• w tabeli DOKUMENTY można też trzymać informacyjnie pola WARTOSC_NETTO, WARTOSC_VAT oraz WARTOSC_BRUTTO (sam tak robię, wtedy od razu widać jaka jest wartość dokumentu bez potrzeby przeliczania)
• w tabeli DOKUMENTY_POZYCJE warto trzymać pole STAWKA_VAT_ID bo stawka VAT może się zmieniać, a dokumenty archiwalne muszą mieć informację o stawce VAT na chwilę wystawiania dokumentu.
• czemu w tabeli DOKUMENTY_POZYCJE pola typu cena, wartość trzymasz jako nchar? Trzeba by używać pola numerycznego.
• w tabeli LEKI trzeba dać pole STAWKA_VAT_ID.

No i warto by było dawać klucze zewnętrzne na pola typu SPOSOB_PLATNOSCI_ID, STAWKA_VAT_ID czy KONTRAHENT_ID po to aby baza sama pilnowała spójności danych, czy też nie było można skasować np. kontrahenta dla którego mamy wystawione dokumenty. Analogicznie można by pomyśleć aby zadbać o unikalność niektórych pól jak numer dokumentu.

Chodzi Ci o klucze obce? np.

 ALTER TABLE DOKUMENTY
ADD CONSTRAINT FK_DOKUMENTY_KONTRAHENT     
 FOREIGN KEY(ID)      
REFERENCES KONTRAHENT(KONTRAHENT_ID) 

Mam nadzieje, że teraz w 99% jest dobrze, to co poprawiłem, jeżeli chodzi o te klucze zewnętrzne (czy obce?) to nie wiem czy nie lepiej byłoby sobie to "wyklinać" już w SQL MSM.

Tak, klucze obce, zewnętrzne. Zależy kto jak woli nazywać. Co do pisania takich rzeczy, to ja z reguły wyklikuję to w jakimś menadżerze do zarządzania bazami. O wiele szybciej i pewniej.

Tak, właśnie o to wyklikanie mi chodziło, dzięki za rady i jak sądzę w pliku z tabelami już błędów nie ma? Dzięki wielkie!

O ile nie masz jakiegoś kolokwium ze składni sql'a gdzie musisz wykazać się znajomością składni to nie ma sensu pisanie z palca instrukcji DML.

Tak jak dla mnie jest wszystko ok :)

Trochę pozmieniałem i jestem na etapie wprowadzania danych, i mam pytanie jeżeli chodzi o DOKUMENTY i DOKUMENTY_POZYCJE . Otóż jeżeli wpisuje dane, to muszę oddzielnie liczyć każdą wartość vat i netto "na boku" czy może masz na to sposób, tak samo wprowadzając dokumenty, można bardzo łatwo popełnić błąd przy takim wklepywaniu? I czy tabelę DOKUMENTY_POZYCJE wykorzystujesz tylko do zliczania stanów magazynowych czy może ma jakieś inne ciekawe zastosowanie?

TABELA DOKUMENTY
 ID 			INT PRIMARY KEY NOT NULL, /id
DOKUMENT_ID 		INT Unique, /unikalny nr dokumentu
TYP_DOKUMENT_ID		nchar(3)	 NOT NULL, /typ dokumentu
KONTRAHENT_ID		nchar(3) NULL, /id kontrahenta
INDYWIDUALNI_ID		nchar(3) NULL, /id klienta
DATA_DOKUMENTU		DATE  NOT NULL, /data dokumentu
WARTOSC_NETTO 		MONEY	 NOT NULL, /wartość netto
WARTOSC_VAT		MONEY	 NOT NULL,  /wartość vat
WARTOSC_BRUTTO		MONEY	 NOT NULL, /wartość brutto
REFUNDACJA		MONEY NULL,                     /refundacja = wartość brutto jeżeli występuje
OPLACONO		BIT	NULL, 
SPOSOB_PLATNOSCI_ID	nchar(3) 	 NOT NULL,

 TABELA DOKUMENTY_POZYCJE
DOKUMENT_ID 		INT PRIMARY KEY NOT NULL, 	
	ID 					INT  NOT NULL, 
	LEK_ID				nchar(3) NOT NULL, 
	CENA_NETTO 			MONEY NOT NULL,
	CENA_BRUTTO			MONEY NOT NULL, 
	ILOSC				int  NOT NULL, 
	ZNAK 				int  NOT NULL, 
	REFUNDACJA 			MONEY NULL, 
	WARTOSC_NETTO 		MONEY  NOT NULL,
	WARTOSC_VAT			MONEY  NOT NULL, 
	WARTOSC_BRUTTO		MONEY  NOT NULL,
	STAWKA_VAT_ID		nchar(3) NOT NULL,

quinek napisał(a):

Otóż jeżeli wpisuje dane, to muszę oddzielnie liczyć każdą wartość vat i netto "na boku" czy może masz na to sposób, tak samo wprowadzając dokumenty, można bardzo łatwo popełnić błąd przy takim wklepywaniu?
W moim systemie wpisuje się tylko cenę jednostkową (netto lub brutto, jak woli użytkownik). System sam wylicza całą resztę. Można by to było załatwić w sumie za pomocą triggerów. Jednak tu wchodzą zaokrąglenia do pełnych groszy które to w aplikacjach finansowych jest nieco inne niż te co standardowo oferuje mój system bazodanowy (chyba, że coś się zmieniło, a pisać oddzielnej procedurki mi się nie chciało). Zatem wszelkie zaokrąglenia oraz przeliczanie dokumentów po zapisie realizuje system zdarzeń wewnątrz modułu głównego aplikacji.

quinek napisał(a):

I czy tabelę DOKUMENTY_POZYCJE wykorzystujesz tylko do zliczania stanów magazynowych czy może ma jakieś inne ciekawe zastosowanie?
Oczywiście. Ta tabela jest w zasadzie podstawowa do większości zestawień jakie posiadam w systemie. Jestem na jej podstawie wykazać jakie obroty (dokumenty przychodowe oraz rozchodowe) miał dany towar. Można tworzyć rankingi które leki są najbardziej chodliwe, itp. Za dużo by pisać, generalnie z tej tabeli da się wyciągnąć wszystko czego potrzebuje firma, jakieś informacje co opłaca się zamawiać, a czego nie zamawiać, ponieważ zalega w magazynie od 5 lat.

Jeszcze jako ciekawostkę powiem, że w przypadku leków bardzo ważna jest seria oraz data ważności. Są to dodatkowe pola jakie mam na tabeli pozycji dokumentów i wypełnia się je wprowadzając fakturę zakupu. Ogólnie ta tabela u mnie zawiera o wiele więcej pól niż w tym przykładzie, tak więc nie sposób wszystko wymienić.