Q:

foreign keys advice in MySQL and Rails

I am working on my first real rails project and am looking for a little professional advice.
I am looking to have four tables in my MySQL database (User, Book, Comments, tags) and the following business rules apply.
A User may make one or many Comments for a Book, but a Comment can only belong to one Book.
A User may place one or many Tags against a book, but  Tag can only belong to one Book.
Graphically, I cam up with the following: (ascii art will never die) 
+---------+             +---------+  
|         |             |         |
|         |             |         |
|  User   |1           1|  Book   |
|         |-----   -----|         |
+---------+     |  |    +---------+
     | 1        |  |          | 1
     |          |  |          |
     |          |  |          |              
     | 1,*      |  |          | 1,*
+---------+     |  | 1,*+---------+  
|         |     ---+----|         |
|         |1,*     |    |         |
| Comment |---------    |   Tag   |
|         |             |         |
+---------+             +---------+

I am looking for any recommendations on how to configure my models in Rails.  At the moment I am looking to setup:
class Users
  has_many :tags
  has_many :comments
  has_many :tagsBooks, :through=>tags, :class_name => "tags_books"
  has_many :commentsBooks, :through=>comments, :class_name => "comments_books"
end

class Books
  has_many :tags
  has_many :comments
  has_many :tagsUsers, :through=>tags, :class_name => "tags_users"
  has_many :commentsUsers, :through=>comments, :class_name => "comments_users"
end

class Tags
  belongs_top :users
  belongs_to :books
end

class Comments
  belongs_top :users
  belongs_to :books
end

I 'believe' this will assist in enforcing foreign key fields (User_id and Books_id) in the Tags and Comments tables - which is ultimately what I need to do.  And I am hoping this will allow me to associate tags and comments to either/both Users and Books ie books.tags or users.tags.
Is this correct?
I am happy to completely change the design as long as foreign keys are enforced in the db and rails.
Cheers,
Damo

A:

This will not enforce foreign keys in the db, they are just integer columns. They only assist in the model layer. t.references also doesn't help. To enforce foreign keys you need to create them manually. I usually do something like this in the migration methods (up or down):
execute <<-SQL
  ALTER TABLE <table_name>
    ADD CONSTRAINT fk_name
    FOREIGN KEY (column_name)
    REFERENCES table_name(id)
SQL

This will create and enforce foreign keys on db.

A:

has_many and belongs_to don't enforce foreign key constraints at the database level. They provide methods that allow you to model relationships, and if you use the methods according to the Rails conventions, all will be well. If you want actual constraints, you need to add that to the database explicitly. (the foreigner gem can help generate the necessary statements).
That said, many (if not most) Rails developers are comfortable with the way Rails handles relationships in the application rather than the database. Your mileage may vary.
Your design looks like it will work with some fixes. I'd change the camelcased names ("tagsBooks" => "tag_books"), which though valid will lose you style points. The :class_name must refer to a real class, so you probably want to do:
has_many :tag_books, :through => :tags, :class_name => "Book"
has_many :comment_books, :through => :comments, :class_name => "Book"

Then you should be able to do:
@book = Book.create
@user.tag_books.create(:book=>@book)

You'll want to read through the Rails docs on has_many and belongs_to. Adding :dependent => :destroy on the join tables is generally a good idea (eg.):
has_many :tags, :dependent => :destroy

