S
S
Swartex2017-01-31 00:54:33
Database design
Swartex, 2017-01-31 00:54:33

Whether correctly implemented structure of a DB?

Hello everyone,
I want to store my library in a DB. We need to keep books and authors.
It seems to me that the relationship between tables should be many-to-many, do I understand correctly?
Or should it still be one-to-many?
Here is my implementation:
57a7ea69419e4eada4d33021299eef67.png
Correct me if I'm wrong :)

UPD
8da518268d1b40238403e6fdadf38aeb.png

Answer the question

In order to leave comments, you need to log in

3 answer(s)
D
DevMan, 2017-01-31
@Swartex

many-to-many, that's right.

S
Stanislav Makarov, 2017-01-31
@Nipheris

id in book_author is not needed. Make a normal composite key from book_id and author_id, everyone will be fine (including the DBMS).

F
Falseclock, 2017-02-23
@Falseclock

The most common mistake of all beginners is naming fields.
Get in the habit of naming the main fields that will be linked in the future with the table name.
if the table is authors, then the key field should be authors_id;
if the table is book, then call the field book_id
Now you have 3 tables and you can understand what goes where.
And when tomorrow you join 5-6 tables, you will simply get confused where and what joins with what.
Here is the most common example with 9 joins. If in all tables there would be fields simply id, I would break my head to join. And this is the most common request. And if you add intersects, crostabs, groupings, conditions and nested queries here, then everything will fall into place in order to intuitively understand where to join with what.

SELECT
        o.order_id,
        lpad(o.order_id::text, 8, '0') AS order_number,
        o.order_date,
        o.order_state,
        o.member_id,
        o.contacts_data_id,
        o.invoice_uuid,
        o.invoice_number,
        z.organization_short_name AS company,
        z.organization_id,
        y.organization_business_number AS bin,
        f.contact_firstname AS firstname,
        f.contact_lastname AS lastname,
        d.*,
        p.*,
        x.supply_id,
        i.invoice_id,
        '2'||lpad(i.order_id::text, 6, '0') AS invoice
      FROM
        orders o
      JOIN contacts_data s ON
        o.contacts_data_id = s.contacts_data_id
      JOIN contacts f ON
        s.contact_id = f.contact_id
      JOIN organizations z ON
        s.organization_id = z.organization_id
      LEFT JOIN organization_data y ON
        z.organization_id = y.organization_id
      JOIN order_data d ON
        o.order_id = d.order_id
      JOIN spare_parts p ON
        p.part_id = d.part_id
      LEFT JOIN supply_data x ON
        x.order_data_id = d.order_data_id
      LEFT JOIN supply xx ON
        xx.supply_id = x.supply_id
      LEFT JOIN invoices i ON
        i.order_id = d.order_id

Move the country plus to a separate table, and indicate the country ID in the authors table.
Tomorrow you want to search by country. Why load the database with text search and build indexes on text fields?

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question