Database design question

Hello, i'm developing a Rails3 application mixing together Gmap4rails
and Devise to build a CMS that allows web developers to make maps
application quickly. The CMS now is based on 2 models.
Location, stores latitude, longitude and name of the marker
Categories, stores the name of the category and the location_id
They are related through a HABTM relationship,
http://guides.rubyonrails.org/association_basics.html#the-has_and_belongs_to_many-association

My question is, how should I add information related to a Location, that
are dipendent on the category?
For example, a Location under the Category "Bar" needs this fields:
"opening_time", "closing_time", "medium_drink_prize" ecc..
A Location under the Category "Hotel" needs this information:
"medium_room_prize", "stars" ecc..
Sometimes the location has 2 categories (has the relationships type self
explained), for examole "Bar", and "Disco", that need both the
Categories field
to be completed.

I think that I should add for every Category a Detail table, that stores
for each field the field's type (file, text, textarea ecc..), and the
name. Category has one Detail, and Detail belongs to Category, but I'm
not sure about.

What would you suggest?
thanks in advance

Have you read the Rails Guide about ActiveRecord Associations? Particularly the part about Has Many Through? That sounds exactly like what you need. The idea there is that instead of the "dumb" HABTM join, with just a join table of two integer columns, you use a full model to describe the relationship. That way your join objects can be decorated with additional data that relate specifically to the connection between the two models they relate.

Walter

Hey Walter, thanks for your reply. Yes I've read this part, but i've
thought that it was not the best solution, probably i was worng.
Today I've saw also this solution, that maybe fit my problem
http://www.unixgods.org/~tilo/Rails/Rails_polymprphic_has_many_through_relationships.html

Please correct me if I'm wrong. You suggest me to do something like
this?

class Location < ActiveRecord::Base
  has_many :details
  has_many :bars, :through => :details
  has_many :discos, :through => :details
end

class Detail < ActiveRecord::Base
  belongs_to :Location
  belongs_to :Bar
  belongs_to :Disco
end

class Bar < ActiveRecord::Base
  has_many :details
  has_many :Location, :through => :details
end

class Disco < ActiveRecord::Base
  has_many :details
  has_many :Location, :through => :details
end

thanks,

Hey Walter, thanks for your reply. Yes I've read this part, but i've
thought that it was not the best solution, probably i was worng.
Today I've saw also this solution, that maybe fit my problem
http://www.unixgods.org/~tilo/Rails/Rails_polymprphic_has_many_through_relationships.html

Please correct me if I'm wrong. You suggest me to do something like
this?

class Location < ActiveRecord::Base
has_many :details
has_many :bars, :through => :details
has_many :discos, :through => :details
end

class Detail < ActiveRecord::Base
belongs_to :Location
belongs_to :Bar
belongs_to :Disco
end

class Bar < ActiveRecord::Base
has_many :details
has_many :Location, :through => :details
end

class Disco < ActiveRecord::Base
has_many :details
has_many :Location, :through => :details
end

thanks,

That sounds about right, but please check your naming very carefully. If you are using normal Rails conventions, then your relationships would look like this

class Bar < ActiveRecord::Base
  has_many :details
  has_many :locations, :through => :details
end

etc. Always name the class Singular, Upper Case, CamelCased and always refer to the plural relationship the same as the table name -- plural, lower-case, and under_scored.

Walter

I would recommend that when you begin designing your database that you
use an ERD (Entity Relationship Diagram) and there are some free
software versions out there - RISE has a free software for instance.
Use the ERD to setup the relationships visually and it will help you
considerably.

It also allows you an image schematic going forward as you start to add
more tables to your database.