How to best structure a database for multiple categories?

Pikachu2000

I may not be able to explain this as clearly as I'd like to, but here goes my best attempt . . .

I am building a DB of businesses and grouping them by type of business. Some businesses may fall into more than one category, for example a car dealer would be included under category listings for 101 (new cars), 102 (used cars), 103 (auto service), and 104 (auto parts).

Would it be better to store the categories in which the business is included in the business_detail table as an array like so:

|  BZ_ID  |  business_name  |  business_address  |  business_category    |
---------------------------------------------------------------------------
|  01234  |Bob's Dealership |  123 some street   |  101, 102, 103, 104  |

Then a table that would list the category ID and its name.

--- OR ---

create a separate table for category_members something like below, and leave the category out of the business_detail table altogether.

|  CM_ID            |  cat_number  |  BZ_ID       |
---------------------------------------------------
|  (PK auto_inc)    |  101         |  01234       |
---------------------------------------------------
|  (PK auto_inc)    |  102         |  01234       |
---------------------------------------------------
|  (PK auto_inc)    |  103         |  01234       |
---------------------------------------------------
|  (PK auto_inc)    |  104         |  01234       |

Or perhaps something completely different that I haven't even thought of?

sneakyimp

The second approach is better because you can use SQL to manipulate it. Strictly speaking, you don't need a CM_ID in that table. You can identify each record by its cat_number and BZ_ID -- you can use them together as a primary key. I call this an 'association table' because you are associating a record in one table with a record in another table.

It's a bit of a hassle to maintain the extra table, but it gives you a lot more flexibility in sql.

Pikachu2000

sneakyimp;10943196 wrote:
The second approach is better because you can use SQL to manipulate it. Strictly speaking, you don't need a CM_ID in that table. You can identify each record by its cat_number and BZ_ID -- you can use them together as a primary key. I call this an 'association table' because you are associating a record in one table with a record in another table.

It's a bit of a hassle to maintain the extra table, but it gives you a lot more flexibility in sql.

OK. That was kind of the way I was leaning, even though it will take a few extra queries. It would make searching for businesses by categories much simpler as well, I suspect. I would just have to remember to add the queries to delete the records if a business delists, or changes categories. Thanks for the advice!

johanafm

Pikachu2000;10943209 wrote:
I would just have to remember to add the queries to delete the records if a business delists

Actually, no. Specify a foreign key constraint with ON DELETE CASCADE, and as soon as you delete a business, all of the business' categories will be deleted as well. The same can be done for a foreign key constraint towards the category table, so that if a category is deleted, all business_category entries for this id will be deleted.

Pikachu2000;10943209 wrote:
, or changes categories. Thanks for the advice!

Which you would have needed to deal with using the other approach as well. But now you just need to delete the record matching this business id and category id from business_category, instead of having to do some funky string manipulation in a business.category field.

Pikachu2000

johanafm;10943281 wrote:
Actually, no. Specify a foreign key constraint with ON DELETE CASCADE, and as soon as you delete a business, all of the business' categories will be deleted as well. The same can be done for a foreign key constraint towards the category table, so that if a category is deleted, all business_category entries for this id will be deleted.

Which you would have needed to deal with using the other approach as well. But now you just need to delete the record matching this business id and category id from business_category, instead of having to do some funky string manipulation in a business.category field.

[YODA]Good points all of those they are.[/YODA]

It's amazing how another set of eyes can sometimes simplify things so much, especially for someone, like me, who always over-thinks things . . . Thanks again.