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:
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,
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.