# Database design - Bussiness/Contacts/Categories etc

**URL:** <https://www.ruby-forum.com/t/database-design-bussiness-contacts-categories-etc/118747>\
**Category:** Rails\
**Created:** [October 30, 2007, 4:29pm UTC](https://www.ruby-forum.com/t/database-design-bussiness-contacts-categories-etc/118747 "2007-10-30T16:29:50Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![josef](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ec9cab/32.png) [@josef](https://www.ruby-forum.com/u/josef)\
**Post date:** [October 30, 2007, 4:29pm UTC](https://www.ruby-forum.com/t/database-design-bussiness-contacts-categories-etc/118747/1 "2007-10-30T16:29:50Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![josef](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ec9cab/32.png) [@josef](https://www.ruby-forum.com/u/josef)\
**Post date:** [October 30, 2007, 8:37pm UTC](https://www.ruby-forum.com/t/database-design-bussiness-contacts-categories-etc/118747/2 "2007-10-30T20:37:42Z")

</div>

On Oct 30, 3:29 pm, Josef D. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
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

---

<div class="post-metadata">

**Author:** ![josef](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ec9cab/32.png) [@josef](https://www.ruby-forum.com/u/josef)\
**Post date:** [October 31, 2007, 1:49pm UTC](https://www.ruby-forum.com/t/database-design-bussiness-contacts-categories-etc/118747/3 "2007-10-31T13:49:35Z")

</div>

> 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. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)