CODEWITHSUNDEEP
mysqlsqlorm
pt0
pt0

Reputation: 175

DB relationship (elegant for any ORM)

I just seek for an advice, what is the best way to a create nice and elegant relationship (that could be used with ORM methods). The content of the database looks like this:

  mysql> describe books;
  +--------+-------------+------+-----+---------+----------------+
  | Field  | Type        | Null | Key | Default | Extra          |
  +--------+-------------+------+-----+---------+----------------+
  | id     | int(11)     | NO   | PRI | NULL    | auto_increment |
  | title  | varchar(45) | YES  | MUL | NULL    |                |
  | author | varchar(45) | YES  |     | NULL    |                |
  | cover  | varchar(45) | YES  |     | NULL    |                |
  +--------+-------------+------+-----+---------+----------------+

  mysql> describe users;
  +----------+-------------+------+-----+---------+----------------+
  | Field    | Type        | Null | Key | Default | Extra          |
  +----------+-------------+------+-----+---------+----------------+
  | id       | int(11)     | NO   | PRI | NULL    | auto_increment |
  | username | varchar(45) | YES  |     | NULL    |                |
  +----------+-------------+------+-----+---------+----------------+

 mysql> describe users_books;
 +---------+---------+------+-----+---------+-------+
 | Field   | Type    | Null | Key | Default | Extra |
 +---------+---------+------+-----+---------+-------+
 | user_id | int(11) | YES  |     | NULL    |       |
 | book_id | int(11) | YES  |     | NULL    |       |
 +---------+---------+------+-----+---------+-------+

 mysql> describe comments;   
 +---------+-------------+------+-----+---------+----------------+
 | Field   | Type        | Null | Key | Default | Extra          |
 +---------+-------------+------+-----+---------+----------------+
 | id      | int(11)     | NO   | PRI | NULL    | auto_increment |
 | body    | varchar(45) | YES  |     | NULL    |                |
 | rate    | varchar(45) | YES  |     | NULL    |                |
 | user_id | int(11)     | YES  |     | NULL    |                |
 +---------+-------------+------+-----+---------+----------------+

 mysql> describe books_comments;
 +------------+-------------+------+-----+---------+-------+
 | Field      | Type        | Null | Key | Default | Extra |
 +------------+-------------+------+-----+---------+-------+
 | book_id    | int(11)     | YES  |     | NULL    |       |
 | comment_id | varchar(45) | YES  |     | NULL    |       |
 +------------+-------------+------+-----+---------+-------+

I don't have any issues with that model, but I think it can be improved. In this way I am fetching the records: 

select body, rate, users.username,title, author from users
inner join users_books on users.id = users_books.user_id
inner join books on books.id = users_books.book_id
inner join books_comments on users_books.book_id = books_comments.book_id
inner join comments on books_comments.comment_id = comments.id
where users.id = 1 and comments.user_id = 1;

While users can have many books, a comment is very specific for a book owned by a specific user. I think that is the point I am not getting it the right way.

Upvotes: 0

Views: 128

Answers (1)

RandomSeed
RandomSeed

Reputation: 29759

Your current structure suggests that a single comment may be about several books (book_comments is a junction table).

But what you want is:

a comment is very specific for a book owned by a specific user

A "book owned by a user" is nothing else but an entry in the users_books table. Drop books_comments. Add to comments a foreign key to users_books' primary key, i.e. (user_id, book_id).

I would create your comments table like this:

CREATE TABLE comments (
    user_id INT NOT NULL,
    book_id INT NOT NULL,
    -- more fields here
    PRIMARY KEY (user_id, book_id)
    FOREIGN KEY (user_id, book_id) REFERENCES users_books (user_id, book_id)
);

This structure assumes that a comment may be posted by a user only on a book that she owns (user_id would be both the book owner and the comment author).

You can add a second foreign key reference to user (e.g. a author_id) to allow any user (author_id) to post a comment on any book (book_id) owned by someone in particular (user_id).

Upvotes: 1

Related Questions