Database design - Bussiness/Contacts/Categories etc

Rails
1

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.

2

On Oct 30, 3:29 pm, Josef D. [email protected]
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

3

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 D. [email protected]