Księga pierwsza: projektowanie bazy danych

Zacznę od pytania: co chcę przechowywać w bazie? I natychmiast odpowiem: na pewno faktury. Zerknięcie na założenia projektowe (pkt. 1) pozwala rozszerzyć odpowiedź. Potrzebował będę również takich tabel jak: klienci, produkty, szablony faktur. Przydałoby się również miejsce na przechowanie danych o użytkownikach programu – tabela sprzedawcy.
Mam już ogólny zarys bazy danych, więc zadaję kolejne pytanie: co powinna zawierać faktura VAT? Odpowiedź, już nie natychmiastową, przynoszą: wikipedia, serwis Moja firma, aplikacja inFakt (na której, nie ukrywam, zamierzam się podczas swojej pracy wzorować) i przykładowa faktura. Takie zagłębienie się w arkana ekonomii daje mi wiedzę potrzebną do stworzenia tabeli Invoices:
1. Id [int] – identyfikator faktury
2. SellerId [int] – sprzedawca [relacja]
3. CustomerId [int] – klient [relacja]
4. Number [nvarchar] – numer kolejny faktury
5. CreationDate [datetime] – data wystawienia faktury
6. SaleDate [datetime] – data sprzedaży
7. PaymentType [nvarchar] – sposób płatności
8. PaymentDeadline [datetime] – termin płatności
9. PaymentCurrency [nvarchar] – waluta
10. ToPay [money] – do zapłaty
11. ToPayInWords [nvarchar] – słownie do zapłaty
12. Paid [money] – zapłacono
13. LeftToPay [money] – pozostało do zapłaty
14. Remarks [nvarchar] – uwagi
15. Status [nvarchar] – status faktury (utworzona / wystawiona / zapłacona)
EDIT: 16. RegisteredSellerId [int] – użytkownik programu [relacja] /EDIT
Nie ma tu pola mówiącego o produktach, ponieważ produkty zostaną połączone z fakturą relacją wiele (produktów) do jednej (faktury).

Jak na razie idzie jak po maś taniej margarynie, a takiej nie można ufać. Dlatego przełknijmy i przetrawmy spokojnie to, co już zaprojektowałem. W utworzonej tabeli pojawiają się perspektywy relacji (np. relacja produkty – faktury), co nie powinno dziwić, ale powinno zastanowić. Wyobraźmy sobie bowiem taką sytuację: użytkownik wystawia fakturę na jakiś produkt, niech to będzie ta tania margaryna. Po miesiącu postanawia jednak podnieść jej cenę. Przy obecnej strukturze bazy danych, taka operacja spowodowałaby zmianę kwoty faktury sprzed miesiąca – hańba! Problem leży w tym, że produkty zdefiniowane przez użytkownika (podatne na edycję) są jednocześnie produktami przypisywanymi do faktur (ich parametrów zmieniać nie należy). Rozwiązaniem może być zastosowanie dwóch tabel produktów:
1. produkty definiowane przez użytkownika,
2. produkty przypisywane do faktur, niezmienne (niedostępne dla użytkownika);
i tak też uczynię. Oczywiście podobnego zabiegu wymagają tabele klientów i sprzedawców.

Możemy zatem zająć się zawartością kolejnych tabel. Podlinkowane wcześniej źródła wiedzy w zupełności wystarczą, w końcu baza będzie zawierać (prawie) jedynie dane potrzebne do wystawienia faktury. Godzinę (lektury, buszowania po inFakt i projektowania) później, mam (tabele Registered to tabele dla danych definiowanych przez użytkownika, pozostałe – dla danych „niezmiennych”, przypisywanych do faktur):

RegisteredSellers:
1. Id [int] – identyfikator sprzedawcy
2. FirstName [nvarchar] – imię sprzedawcy
3. LastName [nvarchar] – nazwisko sprzedacy
4. CompanyName [nvarchar] – nazwa firmy sprzedawcy
5. Street [nvarchar] – ulica [adres firmy]
6. City [nvarchar] – miasto [adres firmy]
7. PostalCode [nvarchar] – kod pocztowy [adres firmy]
8. BankName [nvarchar] – nazwa banku
9. BankAccountNumber [nvarchar] – numer konta firmy
10. BankSwift [nvarchar] – swift banku (nieobowiązkowy)
11. Nip [nvarchar] – NIP sprzedawcy
12. Regon [nvarchar] – REGON firmy (nieobowiązkowy)
13. InvoiceNumberFormat [nvarchar] – format numerów faktur (patrz pkt. 2 założeń projektu)
14. LastInvoiceNumber [nvarchar] – numer kolejny ostatniej faktury

Sellers: (opis kolumn jw.; zawiera tylko dane, które znajdą się na fakturze)
1. Id [int]
2. SellerName [nvarchar]
3. CompanyName [nvarchar]
4. Street [nvarchar]
5. City [nvarchar]
6. PostalCode [nvarchar]
7. BankName [nvarchar]
8. BankAccountNumber [nvarchar]
9. BankSwift [nvarchar]
10. Nip [nvarchar]

RegisteredCustomers:
1. Id [int] – identyfikator klienta
2. CustomerName [nvarchar] – imię i nazwisko klienta
3. CompanyName [nvarchar] – nazwa firmy klienta
4. Street [nvarchar] – ulica [adres firmy]
5. City [nvarchar] – miasto [adres firmy]
6. PostalCode [nvarchar] – kod pocztowy [adres firmy]
7. Nip [nvarchar] – NIP klienta
8. Email [nvarchar] – e-mail klienta (nieobowiązkowy)
9. Www [nvarchar] – strona www klienta (nieobowiązkowa)
10. Phone [nvarchar] – telefon klienta (niebowiązkowy)
11. Remarks [nvarchar] – uwagi (nieobowiązkowe)
EDIT: 12. RegisteredSellerId [int] – użytkownik programu [relacja] /EDIT

Customers: (opis kolumn jw.; zawiera tylko dane, które znajdą się na fakturze)
1. Id [int]
2. CustomerName [nvarchar]
3. CompanyName [nvarchar]
4. Street [nvarchar]
5. City [nvarchar]
6. PostalCode [nvarchar]
7. Nip [nvarchar]

RegisteredProducts: (opis kolumn niżej)
1. Id [int]
2. Name [nvarchar]
3. Pkwiu [nvarchar]
4. MeasureUnit [nvarchar]
5. NetPrice [money]
6. VatRate [nvarchar]
EDIT: 7. RegisteredSellerId [int] – użytkownik programu [relacja] /EDIT

Products: (wyjątkowo zawiera więcej kolumn niż tabela Registered)
– Id [int] – identyfikator produktu
– InvoiceId [int] – faktura [relacja]
– Name [nvarchar] – nazwa produktu
– Pkwiu [nvarchar] – klasyfikacja PKWiU produktu
– MeasureUnit [nvarchar] – jednostka miary produktu
– Quantity [money] – ilość (typ money dla uniknięcia konfliktu typów przy mnożeniu)
– NetPrice [money] – cena netto produktu
– NetValue [money] – wartość netto produktu (ilość * cena)
– VatRate [nvarchar] – stawka VAT produktu
– VatValue [money] – wartość VAT (wartość netto * stawka VAT)
– GrossValue [money] – wartość brutto produktu (wartość netto + wartość VAT)

Trochę się tego uzbierało, nie ma co. Ostatnie elementy podam na sucho, bez barwnego wprowadzenia, bo nie sądzę, żeby którykolwiek z czytelników dobrnął aż tutaj.

InvoicePatterns: (tabela zawierająca szablony faktur)
1. Id [int] – identyfikator szablonu
2. RegisteredSellerId [int] – sprzedawca (z tabeli Registered) [relacja]
3. RegisteredCustomerId [int] – klient (jw.) [relacja]
4. PaymentType [nvarchar] – sposób płatności
5. PaymentCurrency [nvarchar] – waluta
6. Remarks [nvarchar] – uwagi

InvoicePatternRegisteredProducts: (realizuje relację RegisteredProducts – InvoicePatterns)
– InvoicePatternId [int] – szablon faktury [relacja]
– RegisteredProductId [int] – produkt (z tabeli Registered) [relacja]

Na deser (spodziewam się, że niewielu śmiałków dotrwa do deseru – niech żałują!) wypiszę zastosowane relacje:

1. Sellers – Invoices (jeden do jednego)
2. Customers – Invoices (jeden do jednego)
3. Invoices – Products (jeden do wielu)

4. RegisteredSellers – InvoicePatterns (jeden do wielu)
5. RegisteredCustomers – InvoicePatterns (jeden do wielu)
6. RegisteredProducts – InvoicePatterns (wiele do wielu)

Et voila! Winszuję wytrwałym czytelnikom, zapowiadając jednocześnie, że kolejne wpisy nie będą tak długie i nużące.

EDIT:
Przejażdżka rowerem i świeże powietrze sprowadziły na mnie olśnienie: w żadnym miejscu nie przypisałem faktury do użytkownika! Niedopatrzenie naprawiłem dodając kolumnę RegisteredSellerId [int] do tabeli Invoices i relację RegisteredSellers – Invoices (jeden do wielu).
Posłuszny celnej sugestii czytelnika tomash2310, zamieszczam również miły oku schemat bazy danych.

EDIT 2:
Zdałem sobie sprawę, że błąd popełniłem nie tylko w przypadku faktur, ale również klientów i produktów. Dodałem zatem odpowiednie kolumny (RegisteredSellerId [int] do tabel: RegisteredCustomers i RegisteredProducts) i relacje (RegisteredSellers – RegisteredCustomers i RegisteredSellers – RegisteredProducts). Zaktualizowałem też graficzny schemat bazy danych, kończąc tym samym, przynajmniej tymczasowo, pracę nad jej strukturą:

database

Reklamy

8 thoughts on “Księga pierwsza: projektowanie bazy danych”

  1. Może lepiej by było gdyby pokazać tabele i relacje bardziej wizualnie? Np. Programem MySQL Workbench lub podobnymi?:>

  2. Dzięki za sugestię, tak też zrobiłem. Co prawda myślałem już o tym w trakcie pisania, ale zdecydowałem się skomentować każdą kolumnę każdej tabeli – a do tego najwygodniejsza jest forma tekstowa 🙂

  3. Jeśli mogę coś zasugerować, to połączyłbym tabelę seller-ów i customerów. W tej chwili jeśli ktoś raz sprzedaje a raz kupuje to mamy go w dwóch różnych tabelach, niepotrzebnie duplikujemy dane oraz nie mamy pełnej informacji o wszystkich transakcjach danego kontrahenta

  4. I druga sprawa, którą teraz zauważyłem, przykład z salonów samochodowych – na fakturze b. często są pozycje, samochód, przygotowanie samochodu a dopiero na specyfikacji wypisane jest że samochód to model …., kolor….., dodatkowe wyposazenie…. etc. Tutaj nie widzę możliwości zapisania takich rzeczy

  5. @@rek:
    ad. 1: Program z założenia ma być jak najprostszy, na razie nie przewiduję zamiany ról sprzedawcy i klienta, tzn. użytkownik zawsze będzie sprzedawcą wystawiającym fakturę.
    ad. 2: Podejrzewam, że takie przypadki (jak salon samochodowy) wymagają dedykowanych rozwiązań i szablonów faktur, a staram się stworzyć rozwiązanie proste i w miarę uniwersalne. Być może dane, o których piszesz, można umieścić w uwagach do faktury (pole Remarks)?

  6. Jeśli ma być w miarę uniwersalne to moim zdaniem można by od razu przewidzieć na to miejsce w bazie, taka moja sugestia.

Możliwość komentowania jest wyłączona.