Database structure and table logic - need advice

[the182guy]

Hi all,

I need some advice about how to structure my database. It's a MySQL db and I use PHP to interact with it. The project is a business directory.

I have a table which contains all the business info such as name,tel,email,web,text,category. I also have a category table that contains the category names, such as construction, i.t, retail etc.

The problem is, I want to be able to have a business appear under several categories, not just one. At the mo an ID of the category is stored in the business info table, this ID refers to a category name in the category table.

I was thinking i could store more than one value in the category field e.g. '1,4,2,5' then split them when needed. Or having more fields in the table such as cat1,cat2,cat3. What do you think?

Thanks all

[brucevde]

I would suggest you remove the Category from the Business table and add a third table called BusinessCategories

At minimum the table fields would be
BusinessName - or whatever the PK is of the Business Table
CategoryName - or whatever the PK is of the Category Table

Create a unique constraint on BusinessName + CategoryName

add more fields if needed to describe a single Business -> Category relation. IE Active_Indicator, Effective_Date

Using a third table does not put any restrictions on the number of Categories a Business can be placed.

To select all businesses under a specific category is now simply
Select * From BusinessCategories Where CategoryName = "xxxx"

Rather than
Select * From Business Where Cat1 = "xxxx" or Cat2 = "xxxx" or Cat3 = "xxxx"

[the182guy]

thanks for the info theres a couple things i'm not sure about...

1. What does the unique constraint do? I looked it up and seems that is doesnt allow data to be entered that's already in the table.

2. Select * From BusinessCategories Where CategoryName = "xxxx"
Wouldnt this return all the business ID's (PK of businesses table) and also the category IDs? Then I would have to loop through each business ID with the Select * from Businesses Where ID = $ID to get the info about the business? Would this be too inefficient? Or have I missunderstood.

3. Are you saying the third table should look like:

Business ID (PK of businesses), Cat ID (PK of Cats)
1,2
1,4
1,7
2,3
3,7
3,8
4,9

In the above example business #1 would belong to three categories.

Cheers mate

[si_the_geek]

1. You've got it right - it stops duplicate rows of data (which wont do you any good, and will add problems when editing rows).

2. That example was only to get the PK's, to get all the data you would use something like this:

SELECT b.businessname, .... , c.CategoryName
FROM business b
LEFT JOIN BusinessCategories bc ON (b.BusinessID = bc.BusinessID)
INNER JOIN categories c ON (bc.CatID = C.CatID)

Note that the business will be shown 3 times if it belongs to 3 categories, so you would probably load the category data separately, eg:

SELECT c.CategoryName
FROM categories c
LEFT JOIN BusinessCategories bc ON (bc.CatID = C.CatID)
WHERE bc.BusinessID = <the BusinessID from your Business recordset>

3. Yes.

[szlamany]

...Create a unique constraint on BusinessName + CategoryName.

Wouldn't that just be a primary key (actually a composite primary key) on those two fields.

As it stands with the latest post those two columns are.

Business ID (PK of businesses), Cat ID (PK of Cats)

Wouldn't that simply be the primary key - those two columns? That gives you the unique constraint at the same time.

[the182guy]

Thanks all i'll give it a try, I never knew about the JOIN keyword, what I was doing before was loading all of the categories into an array, then extracting the category from the array when printing it to the table.

BTW... is it possible to make a composite primary key in MySQL? I use phpmyadmin to create the db.

Also: Is there any real benefit in doing this (applying unique constraint or composite PK)? I am the only user that will ever admin the database, apart from others that will have limited access through my forms (not phpmyadmin), where I can apply validation to ensure no duplicates are made. I think it's just making things more complicated.