Can this many-to-many table be improved?

Question

I am creating a database where a user can add many books. And a book can be read by many users. I'm using PostgreSQL and set up the following structure:

CREATE TABLE users(
    uid SERIAL PRIMARY KEY,
    email VARCHAR(25) UNIQUE NOT NULL,
    hash TEXT NOT NULL,
    first_name VARCHAR(30),
    last_name VARCHAR(80)
);

CREATE TABLE books(
    bid VARCHAR(30) PRIMARY KEY,
    title VARCHAR(40) NOT NULL,
    author VARCHAR(40) NOT NULL,
    thumbnail TEXT
);

CREATE TABLE users_books(
    user_id INTEGER NOT NULL,
    book_id VARCHAR(30) NOT NULL,
    date_added DATE NOT NULL DEFAULT CURRENT_DATE,
    PRIMARY KEY (user_id, book_id),
    FOREIGN KEY (user_id) REFERENCES users(uid) ON UPDATE CASCADE,
    FOREIGN KEY (book_id) REFERENCES books(bid) ON UPDATE CASCADE
);

As I'm not very familiar with databases, I have the following questions:

1. In the users_books table, does the use of PRIMARY KEY (user_id, book_id) dispense with the use of user_id column or is it still mandatory?
2. Is there any way to auto-populate a table according to the information received by another table? For example, if I feed the users and books tables, can the users_books table feed itself?
3. Is it recommended that I use 3 tables to achieve this goal of people adding books? Or the most recommendable would be to use 2 tables?

Finally, I would like to know what you would do differently from this.

Answer 1

I'm not sure I understand your first question, but I'll give it a shot. The business rule:

A user can add 0 or 1 book would imply a constraint like:

ALTER TABLE users_books PRIMARY KEY (user_id)

On the other hand, a rule like:

Each book can be added by 0 or 1 users, would imply a constraint like:

ALTER TABLE users_books PRIMARY KEY (book_id)

If the rule is that a user can add a book 0 or 1 time, even if the book is already added by some other user, the rule would be:

ALTER TABLE users_books PRIMARY KEY (user_id, book_id)

If the user can add the same book once a day, include date_added in the constraint.

So the answer is, it depends on what BR you would like to implement.

From your description, I would have guessed that that there are two verbs involved:

1. Add_book
2. Read_book

The first would typically be an attribute of the books table:

CREATE TABLE books(
    bid VARCHAR(30) PRIMARY KEY,
    title VARCHAR(40) NOT NULL,
    author VARCHAR(40) NOT NULL,
    thumbnail TEXT,
    added_by_user ... references users (uid)
);

The second would typically be implemented the way you have

The answer to 2 is probably not. The relation represents which users added which books. There is normally no way to figure that out, without the content of user_books table. If user_books is just a cartesian product between users and books, you can replace it with a view:

CREATE VIEW users_books as
SELECT u.uid AS user_id, b.bid AS bookid
FROM users u
CROSS JOIN books b

I think I have answered 3) so I'll add some thoughts

• If possible use well-established standards. In your case, you may consider using ISBN to identify a book

• Use the same identifiers throughout the model. If you are using uid as an identifier for a user, stick with that identifier in other tables in the model

• Try to find meaningful names for your relations. If user_books represents which books have been read by which users. Try to come up with a name that reflects that.

My two cents on how to design a good primary key / unique constraint

It should be:

• Unique -- of course
• Familiar -- it should be something that exists in your business
• Irreduceble -- No subset of the columns should be unique
• Stable -- It should not change frequently
• Non-complex -- It should not consist of too many columns

Often one can not satisfy all of these and one has to choose one over another. Familiar vs Non-complex is often at the stake (I don't normally consider 2 columns too complex).

It might make sense to add a surrogate attribute that can serve as a primary key but don't do this, as a rule of thumb, make a conscious decision.

If you get to the point that the best option is to use a surrogate key, make sure that you add a unique constraint (sometimes referred to as an alternate key) for the natural key.