Resource with a single attribute. Sign that something smells?

I have a开发者_运维百科 book model and a notes model. Each book can have many notes.

# note.rb
id | book_id | content | page_number | author_id |

I want to run a lot of queries like

1. Get all the notes for page 43 of a certain book
2. Show all the noted pages of a certain book

These types of queries seem to favor making a separate noted_pages model so that a book can have many noted_pages and each noted_page can have many notes. This is fine but my noted_pages table would effectively just have an id column and a page_number column which doesn't sit right with me.

Is there a more standard way to implement this kind of setup or is my thinking ok?

A noted_page table would relate notes to pages, but do you need a page table?

If you need a page table, then yes, worry about the note<->page many-to-many relationship and create a link table. If you don't need to store book_pages as rows of a table, then don't.

Your design: id | book_id | content | page_number | author_id |

Will give you the answers you want by querying like this:

1. Get all the notes for page 43 of a certain book

   select * from note where book_id=123 and page_number=43;

2. Show all the noted pages of a certain book

   select page_number, count(id) from note where book_id=123 group by page_number;

If performance is an issue then put an index on the page number. You could also make (id, book_id, page_number) into a composite key, so your data will store (note 3, book 123, page 43).

If you want a many to many relationship, as in a book can have many notes, and notes can have many books, you must have a join table like you stated.

It would look like:

noted_page
--------------
id   book_id note_id

This is just general practice, whenever you have a many-to-many relationship you MUST have a join table.

(Some frameworks/orm's don't require you to have a id column, but as far as I know, Rails/ActiveRecord does require it.)

No design is bad ... it just need to be justified.

I see different solutions :

1. A BOOK {title, author, edited_date ...} has many PAGE {content} has_many NOTES {note_content}
2. A BOOK {content, title, author, edited_date ...} has_many NOTES {note_content, page_number}

It depends on how you work with the book. Do you plan on saving the content of the book by pages or not ? If not, then can you find the page, and therefore the notes ?

Regarding your questions :

• A model with one attribute is not the sign that it "smells". You can even have a 1-to-1 relationship, if it's justifier.
• There's no "standard" way of implementing stuff. Like I said, it depends on how you work, on how you plan your users to use it (UI-wised), how you would store, etc....
• You definatly won't need a many-to-many in your case ... rails provide enough tools t fetch from a model to its associations, w/ w/o conditions, etc...

Let me know if it helps.

Both of these can be simple ARel queries, no scope is required

The latter one is a simple check of the existence of the notes collection:

@book.notes.empty? #if there are no notes, it'll return true and vice versa

To get the book notes for a certain page:

@book.notes.where(["page_number = ?", page_number]) #where page_number is a variable, possibly a parameter

However, I do think you need to think through how you're going to model your data. Marcel has a good point.

Your design is fine. Don't over normalize. You'll probably just want to put an index on book_id, page_number on your notes table so you can do the lookup efficiently.