Database design question

rubyonrails-talk
1

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,

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

2

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

3

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,

4

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

5

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.