Database Design Example | MySQL (Part 2)
Continuation of the article “Database Design Example | MySQL (Part 1)”.
2. Physical design
2.1 Choice of DBMS and other software tools
The developed system can be implemented using any DBMS, including non-relational (NoSQL). NoSQL databases, in turn, are divided into several types:
- column bases and key-value bases are designed to speed up data processing through the implementation of special schemes for storing data in memory;
- document bases allow you to store data with different fields (for different objects) and are better suited for parallel data processing. However, data update is performed slowly.
In our case, the base will be used inside the library and will not require very high performance. The table structure should not change. That’s why, we will use relational databases.
In the collection of articles on the choice of DBMS, it is recommended to choose DBMS running in the cloud if it is difficult to predict the future load, however, at present, almost all popular DBMS as a cloud service. For example, Google Cloud SQL provides PostgreSQL, SQL Server and MySQL. Yandex provides the same functionality, as well as cloud access to ClickHouse, Redis, Kafka, MongoDB.
MySQL is well suited if the amount of data does not exceed 2GB, otherwise — it is better to take a more complex PostgreSQL setup. However, in a simpler database, like ours, it will be enough.
2.2 Compilation and normalization of relational relations
When developing relational relationships, it was found that a number of new fields should be added to a number of database tables. To store dates in MySQL, the DATETIME data type is used, the object of which occupies 8 bytes.
The database schema was created, results in Picture 6.
2.3 Normalization of the received relations
Developed database schema:
- Only scalar values as domains and information in tables is not duplicated. Almost all tables have an identifier (id), and the rest have librarian_rooms and book_places as a primary key, a couple of fields, there is no point in adding the same librarian or book twice to the same room. When re-adding a book — you need to search and change the number of copies in the existing record.
- Every non-key attribute depends irreducibly and non-transitively on the primary key. This is obvious for all tables in our database — Login and Password depend on Id and cannot be displayed otherwise. The number of books and the number of the shelf depend on the id of the book and the id of the room, and they also cannot be displayed in any other way.
2.4 Determination of requirements for the operating environment
In section 2.1, we chose a DBMS, but we didn’t know exactly how much memory we needed to store the tables. In the library, the bulk of the memory will be occupied by books, users, and book issue/booking cards.
For example, the library will receive 100 new (different) books per month and 200 users will sign up. A thousand users will take 3 books. How many books will be booked — it does not matter, because booking cards are destroyed. Considering that 45 * 4 + 4 * 2 = 188 bytes are required to store a record about one book, 184 bytes are required to store a reader, and one card for issuing a book takes 32 bytes, you can determine the approximate amount of memory, required for the library database within one month of work:
100*188 + 200*184 + 1000*3*32 = 18800 + 36800 + 96000 = 151600 bytes = 148 KB. This means that the volume of the database should not exceed 1.73 MB per year.
2.5 Description of user groups and access rights
The database administrator interacts with the database by executing SQL queries. At the same time, he has access to all data, can change the structure of the database, and sets access rights for other groups.
The library room administrator has read and write access to the librarians, rooms, librarians_rooms relationships. If need to work with the library collections, the administrator logs in with a librarian account.
The librarian has access to:
- by reading to relationships: readers, issue_cards, librarians_rooms and rooms;
- for reading and writing to relationships: readers, booking_cards, book_places, books, issue_casrds;
The library reader can interact with the system through a client program installed in the library room or from outside the library through a web interface. It has read access to the relationships: books, book_places, rooms, booking_cards, issue_cards. When working through the client program, the reader also has write access to the issue_cards relation —he can order a book from the reading room.
