The quickest guide to database design

It happened to me in the framework of one project to import an existing database. This base was created in the access and the own essence of the project was to create a web application that provides similar functionality, but taking into account the current realities (web interface, separation of powers, etc.). If we consider in the discussed key, the development was built as follows:

1. create your own system that meets the requirements
2. import data from source database

This note is about item number two.
')
I first encountered a completely non-normalized base. Those. it violated almost all the principles of building relational databases. Nevertheless, this base was used for a long time. I will not go into details, I will only note what caused the first shock - the tables with the names "January", "February", etc. for work schedule. Believe me, then everything was much worse. I understand that it is not for me to judge the person who created it - the system, was used for more than one year and to some extent met the needs of the customer. I just do not want to deal with such "bases" anymore. I hope this article will help in this.

The most brief guide to database design.

As an example, we will design a database for the accounting of goods. With a tree catalog and data on manufacturers.

1. Objects

The first thing to do is to select the types of domain objects. In our case, this is “product”, “catalog section” and “manufacturer”. For each type of its own table is created. Each record (row) of the table contains data about one object. The order of the records is not defined. If the data is added in alphabetical order - when requesting to receive records, this order will be violated.

Avoid duplication of data. For example, it is unacceptable to keep full information about the manufacturer in each entry of the table “product” Because if you change any data of the manufacturer, you will have to look for all references to it in the “goods” table. Let's call our tables item, node company.

2. Primary key

In order to “apply” to a specific object, it is necessary to give it a unique number. Generally speaking, this can be any unique field or group of fields (for example, in the case of employees, a passport number or last name, first name, middle name), but for many reasons it is much more convenient to make a separate field with a unique value. This field is the primary key. Usually this field is called “id” (id).

3. Links, foreign keys

All objects are somehow connected with each other - manufacturers produce goods, goods are placed in a catalog, etc. Relationships are of three types:

one-to-many

one manufacturer can create many different products. It is implemented simply - in the table of objects, which are “many”, a field is created with the object id, which is “one”. In the case of products and manufacturers, you need to add the company_id field in the item table, which will contain the manufacturer's id for the product. Such a field is called an external key .

many-to-many

Any product can be present in several sections of the catalog at once. Such a relationship is stored in a separate table with the product id and section id fields. Thus, each entry in the table indicates the presence of goods in the catalog section.

one to one

let's say our goods are books and CDs. Their general information and type of goods are stored in the item table, and the data specific to books and disks will be stored in the book and disk tables, respectively. Those. For each entry in the book table there is exactly one entry in the item. In fact, this one object is stored in two tables.

It is implemented like this - the primary key of the book table contains the id from the item table. Those. The primary key is also an external key.

tree

in fact, this is also one-to-many. One section of the directory contains many others. The implementation is the same - the node table entry contains the id of the parent section (parent_id)

4. ensuring integrity

All links and keys must be properly described in order to avoid contradictions. Then the base management system will not allow to remove the manufacturer to which the product or the catalog section containing the subsections refers. Other types of reactions are also possible. The main thing is that the base will always be in the correct state, i.e. there will be no foreign keys referencing non-existing entries.

Same thing on SQL

1. create tables

--
create table node (
id numeric not null, --
parent_id numeric not null, -- .
name varchar(200)
);

-- -
create table company (
id numeric not null, --
name varchar(1000),
);

--
create table item (
id numeric not null, --
company_id numeric not null, -- . -
type varchar(10) NOT NULL, -- 'book' 'disc'
name varchar(1000), --
qty numeric, -- -
price numeric --
);


2-3-4. Create missing links and specify which fields are primary and foreign keys.

-- -
create table book (
id numeric not null, -- , item
author varchar(1000)
);

-- -
create table disk (
id numeric not null, -- , item
play_time numeric
);

create table node_item (
node_id numeric not null,
item_id numeric not null
);

--
alter table node add constraint "PK_NODE" primary key (id);
alter table item add constraint "PK_ITEM" primary key (id);
alter table company add constraint "PK_COMPANY" primary key (id);
alter table book add constraint "PK_BOOK" primary key (id);
alter table disk add constraint "PK_DISK" primary key (id);
-- , --, .
alter table node_item add constraint "PK_NODE_ITEM" primary key (node_id, item_id);

--
alter table node add constraint "FK_NODE_PARENT" foreign key (parent_id) references node(id);
alter table item add constraint "FK_ITEM_COMPANY" foreign key (company_id) references company(id);

alter table node_item add constraint "FK_NODEITEM_NODE" foreign key (node_id) references node(id);
alter table node_item add constraint "FK_NODEITEM_ITEM" foreign key (item_id) references item(id);

alter table book add constraint "FK_BOOK_ITEM" foreign key (id) references item(id);
alter table disk add constraint "FK_DISK_ITEM" foreign key (id) references item(id);