Hi, I am just about to embark on my first rails project. I am going to be using Aptana IDE and MySQL and SVN to manage my code. I have been thinking about how to structure my database and would like some pointers. This is what I am trying to acheive; I would like to create a load of businesses, those businesses can have more than one contact, the business will also be categorised and sub-categorisd (I am thinking tagging might be a better option, but lets start with categories) and can also have a number of photos. A contact can also have a photo. So far this is how I have structured it: Table - business id name address city post_code tel fax email website description notes Table - contacts id first_name surname address city post_code tel fax email website business_id Table - photos id caption business_id contact_id Table - categories id name business_id Table - sub_categories id name business_id Table - cities id name business_id contact_id That''s my schema! Should a contact have many businesses or should a business have many contacts? Which way round should I be thinking of these things? Or does either way work. Should a photo have many businesses or should a business have many photos? etc... These are things that I don''t know the answers to when designing my database. Any suggestions or comments please, this is my first rails project and my first ever database, many thanks. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Robin Fisher
2007-Oct-30 19:36 UTC
Re: Database design - Bussiness/Contacts/Categories etc...
On Oct 30, 3:29 pm, Josef Dunne <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> This is what I am trying to acheive; I would like to create a load of > businesses, those businesses can have more than one contact, the > business will also be categorised and sub-categorisd (I am thinking > tagging might be a better option, but lets start with categories) and > can also have a number of photos. A contact can also have a photo.The associations would be as follows: business.rb has_many :contacts has_many :photos has_and_belongs_to_many :categories has_and_belongs_to_many :sub_categories contact.rb belongs_to :business has_many :photos *Could be has_one if they only have one photo category.rb has_and_belongs_to_many :businesses sub_category.rb has_and_belongs_to_many :businesses business_photo.rb belongs_to :business contact_photo.rb belongs_to :contact With regards to the cities, I would change your schema so that the business/contact record "belongs_to" a city. Under your current schema, if you had two separate companies in New York, you would need two entries in the city table. Why are you using a separate city model anyway? Again, with (sub)categories, under your current schema, you could end up with separate entries for the same (sub)category. You need a join table named businesses_(sub)categories with schema: business_id (sub)category_id Your (sub)category table would then just consist of "name" and "id". Also, if the same contact can apply to more than one business, you would need a HABTM association Regards Robin --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
>From what I understand is:1) you will have categories 2) and subcategories that will belong to the categories 3) and you will have businesses that may or may not belong to a subcategory but will definitely belong to a category if this is what you want then you will have to change your categories, subcategories and businsesses table. This will also result in changing your models for these tables. Logically a business would belong to a subcategory which in turn would belong to a category, ie a category can have many subcategories as well as businesses and a subcategory can have many businesses. Correct me if I am wrong? On Oct 30, 3:29 pm, Josef Dunne <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> Hi, > > I am just about to embark on my first rails project. I am going to be > using Aptana IDE and MySQL and SVN to manage my code. I have been > thinking about how to structure my database and would like some > pointers. > > This is what I am trying to acheive; I would like to create a load of > businesses, those businesses can have more than one contact, the > business will also be categorised and sub-categorisd (I am thinking > tagging might be a better option, but lets start with categories) and > can also have a number of photos. A contact can also have a photo. > > So far this is how I have structured it: > > Table - business > > id > name > address > city > post_code > tel > fax > email > website > description > notes > > Table - contacts > > id > first_name > surname > address > city > post_code > tel > fax > email > website > business_id > > Table - photos > > id > caption > business_id > contact_id > > Table - categories > > id > name > business_id > > Table - sub_categories > > id > name > business_id > > Table - cities > > id > name > business_id > contact_id > > That''s my schema! > > Should a contact have many businesses or should a business have many > contacts? Which way round should I be thinking of these things? Or does > either way work. Should a photo have many businesses or should a > business have many photos? etc... These are things that I don''t know the > answers to when designing my database. > > Any suggestions or comments please, this is my first rails project and > my first ever database, many thanks. > -- > Posted viahttp://www.ruby-forum.com/.--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---