Storage of multiple categories into a single unit

[$Bird$]

I am looking for an efficient solution to the storage of multiple categories to a single business. What is, is that I have 2 tables (one of the main categories and one with subcategories). In these two tables, of course, each category has a name, a description and an ID (primary key). When this is set up so I only want to save Category_id to the business table and either JOIN them with business_id or run your own query to retrieve the category name. The reason why I wonder so much of this is due. I feel it is unnecessary to have a separate table set up as follows:

maincategory_id | subcategory_id | business_id

where there is a category rates. row. If so unnecessary "many" rows 1000 if businesses have 5 categories.

So what I thought was to write all category_id to one or two separate fields in the business table, separated by "," (comma) and then to run such a small matter to place them nicely in a list or something:

<?php
$cat = array();
$cat[] = explode(',',$get['category']);
foreach $cat AS $value { ... }  //  or something like that works
?>

But in connection with the search/appraisal of results, based on the category, where I used this SQL query, I got a problem:

<?php
$sql = mysql_query('SELECT * FROM businesses WHERE category LIKE %'.$_POST['category_id'].'%');
?>

What is, is that the tabs 9, 19, 29 ... etc / etc. All contain the number 9. What naturally happens is that a business will appear in too many / incorrect result views. Alternative I can write all the names of the categories this field, but I think that also is not so easy.

So what I wonder is how this can be solved - many do the like everywhere, there is certainly a "standard" on it. I reckon that websites like my assignments, and other tender services have not listed one per row. Have searched a bit but can not find anything like that right around the structure/layout.

Does someone have come up with some tips there?

[fellah]

How you have modeled it is not any connection between the main and sub-category, if I understand you right. So, you've both maincategory_id and subcategory_id in a separate table, and this is linked back to business, through business_id.

It provides the ability to link the activities of the main category 'fish' and subcategory 'sparrow'. But you may want to refine this so that the category must be related to the main category?

You can move maincategory_id from the table of categories and associations in the table below category. Then you can tie the business to another category, and this is already, linked to a main category. You might also consider whether you will have all categories in one table and just add a field for "over category," so you have no restriction on the category levels.

But when you have such many-to-many relationships are necessarily many rows of linking tables. If you want to put the category ID's in a field as a comma separated list as you type, so it is possible, but when you're on a show on the road away from using relational database, and you might as well cram everything in a flat file. So I encourage you to think as you have done until now.

Are not quite sure I understand your data model, but at least it sounds a bit wrong that you can associate the categories to the different main categories depending on the activity in question.

[$Bird$]

Not so good at explaining it but try one more time here. This is the table structure:

businesses
> Business_id
> maincategory_id
> Subcategory_id

maincategory
> MCategory_id
> MCategory_name
> MCategory_description

subcategories
> SCategory_id
> SCategory_name
> SCategory_description
> Rel_MCategory_id

What is the "goal" is to store more category_id to a field, separated by "," (comma). In the resulting view is it possible to filter the results based on category - true. And some businesses to appear under multiple categories.
The problem is the following:
Category 41, 42, 43 contains the digits 4, 1, 2, 3 - Then, run a LIKE statement in sql query results that a business is shown in the categories: 1, 2, 3, 4, 41, 42, 42 which of course is wrong.

Alternatively would like to create a completely separate table as follows:
businesses_categories
> MCategory_id
> SCategory_id
> Business_id

The solution then is to save one per row. JOIN categories and with businessesTable. It could result in a huge number of rows (not that it has so much to say since only IDs, but must be a smart solution for this?) Flat files you mention - Can I restrict a result display based on flat file?

[fellah]

So why not use the relational database to what it is good instead of cool in that way there? Yes you can restrict the view from a flat file, it's just to program the way you want it for yourself ... pointless, but ...

What you need to do is sit down and think about whether to use a relational database at all. And if you decide that you want to do it, so use it as it is intended. For many-to-many relationship between business and category, then you necessarily have any rows in the related table. The big advantage is that it's easy to join and filter, rather than fiddle with anything with a flat files and/or weird comma lists.

Otherwise, I am still not sure whether you should have maincategory_id in businesses_categories table AND in the categories table, then you get the chance to save the constellations that does not quite add up. Take and google "normalize relational database" and you'll find some useful info on this.