Hello everyone,

I am making a database where businesses are listed and displayed on a web page (PHP-MySQL). Each business has to be in a category, like "Joe's Mechanics" is in the "Automotive services" category.

The problem I have is that I don't know the best way to design a table that supports a business being in multiple categories!

For example: the business "The Mannog Place" is in the category "Restaurant" and also in the categories "Accommodation" and "Sport and Recreation" because is has all of these facilities.

I have 30+ categories here and I need to know how best to design the table to handle them. Do I have just a 'categories' column with each category the business is in separated by commas in a long string? Or do I have 30+ colums all being a separate category with a 'yes' in them if the business is part of that category?

It seems so inefficient! Both of my ideas do.

Can anyone with more experience please help me out here?

Thanks!!

You can make 3 tables. One for the business, one for the categories, and then the last one will be the relationship.

My first thought would be to have a table with the businesses in it. In the table give each a unique number (indexed unique).

The second table would be the various categories with each category having a unique number(indexed unique).

A third table that just have the unique business# and the categories (both fields indexed, non unique obviously). That way, any business could be in any number of categories.

This is just one of many ways this could be accomplished.

Hope this helps

Excel28: I'm sorry but I don't understand the concept you're alluding to. What is the relationship table?

docalton: I don't understand your method either - treat me like a n00b, I have very limited understanding of MySQL and database systems in general. So if you could explain your method a bit more it would be much appreciated!

Thanks for your very quick response guys!

What excel28 said and what i said are pretty much the same. He's just not a long winded as I. (jealousy rears its ugly head)


Business table

busid name address whatever other fields you need
------------- ---------------- ---------------- -------------------------------
1 chicken castle 1 bockbock drive
2 MS 2 bigbucks lane



category table

catid Category
------------- ----------------
1 Dining
2 Monopolistic Dictator
3 Spyware Attractor


Relation Table

busid catid
------------ ------------
1 1
2 2
2 3

The relation table essentially tells how to relate the business table and the category table
In this case chicken castle is a dining establishment, while MS is listed as both a Monopolistic Dictator and a Spyware Attractor.

Hope this helps

Great, thankyou for explaining that so well! Very easy to understand.

Its exactly what I needed.

Thanks again for your help!

Another related question:

With the three databases, do I need Foreign Keys and References?

Never heard of "references" in this respect, however, "foreign keys enforce referential integrity at the database level", that is, i.e., ensuring a category "in use" cannot be removed from the database. Alternatively, you can write PHP code, too, that checks necessary pre-conditions, however, it's good practice to modularize software applications -- to solve problems where they arise.

- As for the three tables, I would use two. In the business table, there would be a field for the category ID.

An Introduction to Database Normalization

You would definately want to use foreign keys to ensure you cannot erase categories that are still in use etc.

On the database design, you could use 2 tables, 1 for business and 1 for categories. But if you are storing much data on each business with the ability of multiple categories, using 2 tables would add a lot of redundant data and end up inflating the size of your database(s).

Just for future reference, try not to confuse using the word 'database', when you mean 'table' (& vice versa).
There are situations where this can lead to problems.
Note that a single 'physical' database eg a MySQL install will normally contain multiple 'logical' databases known as schemas, each owned by a separate user.
HTH

For future reference, this middle table is known as a resolver table. Its job is to resolve the many-to-many relationship that you have between business and category. If you do a search on google with resolver table mysql php tutorial then you should get a number of helpful sites that will explain this table from different angles.