T-SQL: pierwsze tabele, relacja 1-do-1

 

Do dalszej pracy nad projektem niezbędna będzie poprawnie zaprojektowana tabela. W tym momencie projekt całej bazy, aczkolwiek przydatny, nie jest niezbędny. Przed utworzeniem bazy dla swojej aplikacji chciałbym najpierw poszerzyć swoją wiedzę z zakresu modelowania danych. Do dalszych rozważań, pojedyncza tabela (a właściwie dwie ze względu na powiązanie ich), której projekt przedstawię w niniejszym poście, będzie wystarczająca.

Celem do zrealizowania jest utworzenie tabeli przechowującej dane o sklepach. Sklep może mieć swoją nazwę oraz należeć do sieci handlowej. Ponadto sklep oczywiście musi znajdować się w określonym mieście oraz mieć przypisany konkretny adres. Tutaj występuje pewne ułatwienie w stosunku np. do tabeli przechowującej dane osoby, gdyż sklep może mieć tylko jeden adres. Wszystkie te dane muszą być określone. Nie da się przecież odnaleźć sklepu nie znając jego nazwy i adresu. Jedynym atrybutem, który nie musi być znany jest nazwa sieci handlowej. Z racji tego, że aplikacja ma umożliwiać jedynie zlokalizowanie sklepu na mapie, nie są potrzebne dodatkowe dane takie jak kod pocztowy, czy też telefon. W razie przeoczenia jakiegoś atrybutu adresu pozostawiam sobie możliwość jego dodania. W związku z tym dane adresowe zostaną umieszczone w innej tabeli niż podstawowe dane sklepu, które również potem w razie potrzeby mogą być uzupełnione. Powstaną więc dwie tabele, pierwsza  o nazwie Shops oraz druga o nazwie Addresses. Tabele te muszą być w relacji 1-do-1 ze względu na to, że pod jednym adresem może istnieć tylko jeden sklep (nawet jeśli są na tej samej ulicy i pod tym samym numerem to z reguły jest jeszcze dodatek w postaci określenia lokalu lok., literka dodana na końcu, czy też inne oznaczenie). Analogicznie sklep z powodu wymagań administracyjnych również może mieć przypisany tylko jeden adres. Zatem do dzieła.

Tworzenie tabeli Shops:

USE ShoppingAppDb;
GO

IF OBJECT_ID('dbo.Shops') IS NOT NULL
    DROP TABLE dbo.Shops;
    
CREATE TABLE Shops
(
    Shop_Id int IDENTITY(1,1) not null primary key,
    Name varchar(50) not null,
    Addr_Id int unique not null, -- 1-to-1 relation
    ChainName varchar(50) null
);

Przed utworzeniem tabeli należy oczywiście sprawdzić, czy nie istnieje już tabela o takiej nazwie. W tym celu wykorzystywana jest funkcja OBJECT_ID(), która jako parametr przyjmuje ciąg znakowy określający nazwę tabeli (w ten sposób można również sprawdzać fakt istnienia innych obiektów). Kolumna Shop_Id jednoznacznie musi określać dany sklep w bazie. Z tego powodu posiada ona atrybut IDENTITY(1, 1), który oznacza, że numeracją poszczególnych wierszy obarczymy SQL Server, przy czym wiersze numerowane będą od numeru 1, z krokiem inkrementacyjnym równym 1. Podkreślić należy, że nie oznacza to, że wszystkie w kolejności numery porządkowe zostaną wykorzystane. Zapewniona jest jedynie ich unikalność. W kolumnie tej nie mogą znajdować się wartości NULL, a ponadto jej cechy decydują o tym, że warto w niej założyć klucz główny (w tym momencie nie potrzebuję klucza nazwanego, być może zostanie to zweryfikowane w późniejszym czasie).

Tworzenie tabeli Addresses:

IF OBJECT_ID('dbo.Addresses') IS NOT NULL
    DROP TABLE dbo.Addresses;
    
CREATE TABLE Addresses
(
    Addr_Id int IDENTITY(1,1) not null primary key,
    City varchar(30) not null, 
    Street varchar(40) not null,
);

Tabela z adresami również posiada na tej samej zasadzie utworzony klucz główny. Kolumny City oraz Street muszą być określone, natomiast w kolumnie Street przechowywany będzie cały adres, łącznie z numerem (ponownie na tę chwilę nie widzę konieczności rozgraniczania numeru ulicy od jej nazwy z powodów wcześniej wymienionych, dotyczących różnorodności sposobów określania adresów).
 
Przy zwracaniu wyników zapytań z tabeli Shops, chcielibyśmy otrzymywać adres w postaci ciągu znakowego, a nie jedynie numeru określającego adres. Ponadto dodanie nowego sklepu wymaga podania adresu. Z tych powodów należy powiązać ze sobą te dwie tabele za pomocą klucza obcego:
ALTER TABLE dbo.Shops 
ADD CONSTRAINT FK_shops_addresses FOREIGN KEY(Addr_Id)
REFERENCES dbo.Addresses (Addr_Id)
ON DELETE NO ACTION
ON UPDATE CASCADE;

Po wywołaniu powyższego kodu klucz obcy powstaje w kolumnie Addr_Id w tabeli Shops, która to jest powiązana z kolumną Addr_Id z tabeli Addresses.
 
W tym momencie można by ulec pokusie, że rekordy w tabeli Shops oraz Addresses nie mogą się dublować, w związku z czym można zakończyc pracę nad tymi tabelami. Jednak nic bardziej mylnego. Najpierw odnośnie drugiej z tabel czyli Addresses – to, że kolejne wiersze z tabeli będą posiadać unikatowe identyfikatory nie oznacza jeszcze, że dane w niej nie mogą się powtarzać. Przy takim zdefiniowaniu tabeli łatwo można bowiem dodać dwa razy tę samą nazwę miasta (co akurat jest pożądane) oraz ten sam adres (co już nie jest pożądane, gdyż powoduje duplikowanie rekordów). Należy zatem uniemożliwić takie działanie poprzez dodanie ograniczenia unikalności (Unique Constraint), które nie pozwoli na ponowne wpisanie tej samej kombinacji miasta i adresu:
ALTER TABLE dbo.Addresses
ADD CONSTRAINT UQ_ADDRESSES UNIQUE(City, Street);

Wykonane, ale czy to zapewnia realizację postawionego na początku celu? Tak, ale należy zwrócić baczną uwagę na pewien drobny szczegół, jakim jest atrybut unique dodany do definicji kolumny Addr_Id w tabeli Shops. Aby przykład był bardziej dydaktyczny nie powinienem umieszczać go w pierwszym kodzie źródłowym ale, ze względu na zbytnie nie rozwlekanie postów, zdecydowałem się go tam już na początku umieścić. Wyobraźmy sobie przez chwilę, że nie ma tam słowa unique. Co w takim wypadku otrzymujemy? Otóż, o ile jeden sklep w dalszym ciągu może posiadać tylko jeden adres, o tyle dodanie drugiego sklepu pozwala ponownie na przypisanie tej samej wartości z tabeli Addresses, co oznacza, że dwa różne sklepy mogłyby istnieć pod tym samym adresem. Takiej operacji jednak już na początku nie uznaliśmy za poprawną. Dodanie atrybutu unique sprawia, że wartości w tej kolumnie nie mogą się dublować, a co za tym idzie nie można dwóm różnym sklepów przypisać tych samych adresów (dla przypomnienia unikatowość danych w tabeli Addresses została już wcześniej zapewniona). Sprawdzenie tego faktu pozwalam sobie pominąć w tym poście (sprawdzenie takiego oczywiście dokonałem w swojej bazie).
 
Na zakończenie tworzenia tabel chciałbym poruszyć jeszcze jedną istotną kwestię. Dodanie danych do tabeli ze sklepami wymaga wcześniejszego istnienia adresu w tabeli z adresami (ograniczenie klucza obcego). Po pierwsze na użytkownika nie powinno się nakładać obowiązku dowiadywania się co do jakiej tabeli należy wstawić, aby można było coś wstawić do innej tabeli. Ponadto, ze względów bezpieczeństwa, nie powinno się umożliwiać swobodnego dokonywania operacji INSERT, UPDATE, czy też DELETE. Wypadałoby stworzyć więc procedurę składowaną (Stored Procedure), która będzie ukrywać sposób realizacji procesu wstawiania danych przed użytkownikiem.:
CREATE PROCEDURE sp_InsertShopProcedure
(
    -- input parameters
    @Name varchar(50),
    @ChainName varchar(50),
    @City varchar(30),
    @Street varchar(40)
)
AS
BEGIN
    INSERT INTO dbo.Addresses(City, Street) VALUES (@City, @Street);
    DECLARE @id int = SCOPE_IDENTITY();
    
    INSERT INTO dbo.Shops (Name, ChainName, Addr_Id) VALUES (@Name, @ChainName, @id);

END;

W powyższej procedurze dane uzyskane od użytkownika, czyli nazwa sklepu, sieci, miasto oraz jego adres, wykorzystywane są w takiej kolejności, że najpierw następuje dodanie miasta i adresu do tabeli Addresses, co umożliwia z kolei wykorzystanie tych danych do wstawienia sklepu do tabeli Shops. Po wstawieniu danych do tabeli Addresses uzyskiwany za pomocą funkcji SCOPE_IDENTITY() jest identyfikator wstawionego wiersza w ramach tej procedury do wspomnianej tabeli, który z kolei umożliwia poprawne wstawienie danych do tabeli Shops. Calość można by umieścić jeszcze w ramach transakcji, czego w powyższym kodzie nie uczyniłem. Wykorzystanie opisanej tabeli przedstawię w kolejnych postach. Tym razem już na zakończenie – diagram z utworzonymi tabelami:

tabele

Reklamy

3 Responses to T-SQL: pierwsze tabele, relacja 1-do-1

  1. macius says:

    🙂

  2. bartos says:

    Witam mam pytanie jak Pan zrobił, że w diagramie widnieje zapis 1 do 1 ? Bo od jakiegos czasu też próbuje zrobić relację 1 do 1 bądź wiele do wielu i tworząc taki diagram nie ma tam za dużo opcji żeby to zrobić ;/. Czy konieczne jest pisanie kodu ręcznie ? Dziwne żeby visual web dev 10 nie miał takiej funkcji żeby można było ustawić takie relacje w momencie tworzenia diagramu.

    • Diagram został wygenerowany w SQL Management Studio – w tym przypadku tabele w bazie i relacja pomiędzy nimi były utworzone. Management Studio wygenerowało zatem diagram wg. własnych reguł bez żadnej ingerencji z mojej strony 🙂

Skomentuj

Wprowadź swoje dane lub kliknij jedną z tych ikon, aby się zalogować:

Logo WordPress.com

Komentujesz korzystając z konta WordPress.com. Wyloguj / Zmień )

Zdjęcie z Twittera

Komentujesz korzystając z konta Twitter. Wyloguj / Zmień )

Zdjęcie na Facebooku

Komentujesz korzystając z konta Facebook. Wyloguj / Zmień )

Zdjęcie na Google+

Komentujesz korzystając z konta Google+. Wyloguj / Zmień )

Connecting to %s

%d blogerów lubi to: