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ą: