Table for Catagory / Subcatagory

[webgodjj]

I have a simple (?) question regarding good database design. I am creating a business database. The important part of this database is that a company can belong to one catagory, and one or multiple subcatagories.

Ie.
Catagory: Restaurant
Subcatagory: Italian, Pizza, Fastfood

As it stood, there were going to be only one catagory and one subcatagory so I made the table as below. Now I see that I need to make another table, I think? There are going to be somewhere in the neighborhood of 5,000 business in the end.

DROP TABLE IF EXISTS Business;

CREATE TABLE Business (

  bid mediumint unsigned NOT NULL auto_increment,

  b_name varchar(50) NOT NULL,

  b_address varchar(100) NOT NULL,

  b_city varchar(30) NOT NULL,

  b_state varchar(3) NOT NULL,

  b_zip int(12) NOT NULL,

  b_county varchar(30) NOT NULL,

  b_phone int(11) NOT NULL,

  b_cat varchar(50) NOT NULL,

  b_subcat varchar(50) NOT NULL,

  b_contact_first varchar(20) NOT NULL,

  b_contact_last varchar(30) NOT NULL,

  b_website varchar(100) NOT NULL,

  b_email varchar(100) NOT NULL,

  b_image varchar(100) NOT NULL,

  b_paid varchar (3) NOT NULL,

  b_comment text NOT NULL,

  b_datemodified TIMESTAMP(8),

  PRIMARY KEY  (bid)

); 

[freakysid]

Here is how I would do it.

Three tables:

Business, Category, Business_Category

CREATE TABLE Business (
b_id mediumint unsigned NOT NULL auto_increment,
... etc, but don't include the b_cat or b_subcat
PRIMARY KEY (b_id)
);

CREATE TABLE Category (
name varchar(100) NOT NULL,
parent varchar(100) NOT NULL,
PRIMARY KEY (name, parent)
);

CREATE TABLE Business_Category (
b_id mediumint unsigned NOT NULL,
category varchar(100) NOT NULL,
PRIMARY KEY (b_id, category)
);

Table Category works like this;

-----------------------------
name           | parent
-----------------------------
Restaurant     | none
-----------------------------
Programming    | none
-----------------------------
Fastfood       | Restaurant
-----------------------------
Italian        | Restaurant
-----------------------------
Pizza          | Restaurant
-----------------------------
php            | Programming
-----------------------------

where "none" is a reserved word in your data model signifying that the category has no parent - it is a primary node. This is a clasic data tree structure.

Now for your example above you would expect the data in Business_Category to look like this:

----------------------------
b_id        | category
-----------------------------
1           | Italian
-----------------------------
1           | FastFood
-----------------------------
1           | Piza
-----------------------------

[webgodjj]

I almost get you... a couple of questions.

1. I thought primary keys were supposed to be something unique to each record?

PHP
2. When looking up records. I am really confused how I would create an sql statement that would do:
a. Find business in a certain catagory ie. list all business with the catagory of restaurant.
b. Find the business in a sub catagory. Ie. Find all Pizza restarunts

Then.. when creating records, I would have to do use mysql_insert_id() to create the b_id for the Business_Category table?

[freakysid]

1. I thought primary keys were supposed to be something unique to each record?
That's a common misconception. Lots of people have an artificial key - like a auto_increment int "id" but it is not necessary. You can have composite primary keys and in the case of Business_Category it is necessary.


2. When looking up records. I am really confused how I would create an sql statement that would do:
a. Find business in a certain catagory ie. list all business with the catagory of restaurant.

SELECT DISTINCT b_name, Category.Parent AS Cateogory_Name
FROM Business, Business_Category, Category, Category AS Subcats
WHERE Category.parent = 'Restaurant'
AND Category.name = Subcats.name
AND Business_Category.category = Subcats.name
AND Business.b_id = Business_Category.b_id;

b. Find the business in a sub catagory. Ie. Find all Pizza restarunts

SELECT b_name
FROM Business, Business_Category
WHERE Business.b_id = Business_Category.b_id
AND category = 'Pizza';

Then.. when creating records, I would have to do use mysql_insert_id() to create the b_id for the Business_Category table?

Yes

Here is a test of the database schema:

CREATE TABLE Business (
b_id mediumint unsigned NOT NULL auto_increment,
b_name varchar(100) NOT NULL,
PRIMARY KEY (b_id)
);

CREATE TABLE Category (
name varchar(100) NOT NULL,
parent varchar(100) NOT NULL,
PRIMARY KEY (name, parent)
);

CREATE TABLE Business_Category (
b_id mediumint unsigned NOT NULL,
category varchar(100) NOT NULL,
PRIMARY KEY (b_id, category)
);

INSERT INTO Business VALUES
(NULL, 'Pippo\'s Pizza'), 
(NULL, 'Greasy Joe\'s'),
(NULL, 'FreakySid\s SQL Service');

INSERT INTO Category VALUES 
('Restaurant', 'none'), 
('Italian', 'Restaurant'), 
('Pizza', 'Restaurant'), 
('FastFood', 'Restaurant'),
('Database Related', 'none');

// categories for Pippo's Pizza
INSERT INTO Business_Category VALUES
(1, 'Italian'), 
(1, 'Pizza');

// categories for Greasy Joe's
INSERT INTO Business_Category VALUES 
(2, 'FastFood');

// categories for FreakySid's SQL Service
INSERT INTO Business_Category VALUES 
(3, 'Database Related');

mysql> select * from Business;
+------+------------------------+
| b_id | b_name                 |
+------+------------------------+
|    1 | Pippo's Pizza          |
|    2 | Greasy Joe's           |
|    3 | FreakySids SQL Service |
+------+------------------------+

mysql> select * from Category;
+------------------+------------+
| name             | parent     |
+------------------+------------+
| Database Related | none       |
| FastFood         | Restaurant |
| Italian          | Restaurant |
| Pizza            | Restaurant |
| Restaurant       | none       |
+------------------+------------+

 mysql> select * from Business_Category;
+------+------------------+
| b_id | category         |
+------+------------------+
|    1 | Italian          |
|    1 | Pizza            |
|    2 | FastFood         |
|    3 | Database Related |
+------+------------------+

mysql> SELECT DISTINCT b_name, Category.Parent AS Cateogory_Name
    -> FROM Business, Business_Category, Category, Category AS Subcats
    -> WHERE Category.parent = 'Restaurant'
    -> AND Category.name = Subcats.name
    -> AND Business_Category.category = Subcats.name
    -> AND Business.b_id = Business_Category.b_id;
+---------------+----------------+
| b_name        | Cateogory_Name |
+---------------+----------------+
| Pippo's Pizza | Restaurant     |
| Greasy Joe's  | Restaurant     |
+---------------+----------------+

mysql> SELECT b_name, Category.Parent AS Cateogory_Name, Subcats.name AS Subcategory_Name
    -> FROM Business, Business_Category, Category, Category AS Subcats
    -> WHERE Category.parent = 'Restaurant'
    -> AND Category.name = Subcats.name
    -> AND Business_Category.category = Subcats.name
    -> AND Business.b_id = Business_Category.b_id;
+---------------+----------------+------------------+
| b_name        | Cateogory_Name | Subcategory_Name |
+---------------+----------------+------------------+
| Pippo's Pizza | Restaurant     | Italian          |
| Pippo's Pizza | Restaurant     | Pizza            |
| Greasy Joe's  | Restaurant     | FastFood         |
+---------------+----------------+------------------+

mysql> SELECT b_name
    -> FROM Business, Business_Category
    -> WHERE Business.b_id = Business_Category.b_id
    -> AND category = 'Pizza';
+---------------+
| b_name        |
+---------------+
| Pippo's Pizza |
+---------------+

[webgodjj]

Whoa... that's one thorough reply
I'll give it a try and see how things work out.

CHeers,

JJ

[freakysid]

Oops! Watch out when cutting and pasting the SQL above - note that vBulletin removed the \ before the ' in Joe's Pizza, etc.

[webgodjj]

Here's a loop.. what if a business has a Category, but no Subcategory? For example some business, like Hotel, won't have a Subcategory... Any thoughts what I would do with this? Or a Restaurant might not be classified yet, but classified later....

Would I just put them into the from Business_Category;

+------+------------------+
| b_id | category         |
+------+------------------+
|    1 | Restaurant       |

[webgodjj]

Ok.. I gave it a try and had to update the sql statement... it works, but is this the wrong way to do things?

mysql> select * from Business;
+------+------------------------+
| b_id | b_name                 |
+------+------------------------+
|    1 | Pippos Pizza           |
|    2 | Greasy Joes            |
|    3 | FreakySids SQL Service |
|    4 | French Connection      |
+------+------------------------+
4 rows in set (0.00 sec)

mysql> select * from Business_Category;
+------+------------------+
| b_id | category         |
+------+------------------+
|    1 | Italian          |
|    1 | Pizza            |
|    2 | FastFood         |
|    3 | Database Related |
|    4 | Restaurant       |
+------+------------------+
5 rows in set (0.00 sec)

mysql> SELECT DISTINCT b_name, Category.Parent AS Cateogory_Name FROM Business, Business_Category, Category, Category AS Subcats WHERE (Category.parent = 'Restaurant' OR Category.name = 'Restaurant') AND Category.name = Subcats.name AND Business_Category.category = Subcats.name AND Business.b_id = Business_Category.b_id;+-------------------+----------------+
| b_name            | Cateogory_Name |
+-------------------+----------------+
| Pippos Pizza      | Restaurant     |
| Greasy Joes       | Restaurant     |
| French Connection | none           |
+-------------------+----------------+
3 rows in set (0.00 sec)

[r937]

freakysid, i can tell you've done this before

nice job

however, there are a couple things i'd like to tweak

1. I thought primary keys were supposed to be something unique to each record?
That's a common misconception. Lots of people have an artificial key - like a auto_increment int "id" but it is not necessary. You can have composite primary keys and in the case of Business_Category it is necessary.

actually, webgodjj's original statement is true -- by definition, a primary key must be unique

your Business_Category has a perfectly good primary key definition, a composite of name and parent

however, this structure implies that the same subcategory can belong to more than one parent, i.e. the pairs {foo,bar} and {foo,qux} are distinct, therefore good primary key values, and yet foo belongs to two parents

that's a sophistication that one doesn't normally find in a category/subcategory structure, which is more commonly implemented as a strict hierarchy, i.e. each subcategory belongs to at most one parent

even in cases where a subcategory appears to belong to more than one parent category, it often does not

for example, do a search on the Open Directory Project (http://dmoz.org/) for "Programming," and you will find many categories, including:

- Computers: Programming
- Computers: Education: Programming
- Computers: Parallel Computing: Programming

these are not the same subcategories, although they all have the same name!

second tweak: i don't think your join is correct

you have

FROM Business, Business_Category
, Category, Category AS Subcats
WHERE Category.parent = 'Restaurant'
AND Category.name = Subcats.name
AND Business_Category.category = Subcats.name
AND Business.b_id = Business_Category.b_id;

how can you have Category.name = Subcats.name?

surely this should be Category.name = Subcats.parent

as for your latest question, webgodjj, "Here's a loop.. what if a business has a Category, but no Subcategory?" it may help to think of the businesses as being allowed to hang off of any level of the category tree

so yeah, link a business to the top level Restaurants, if that's what you want

the tree analogy is particularly apt because the category/subcategory hierarchy can be seen as one or more "roots" (identified by NULL or nonexistent parents) and "branches" (always drawn in diagrams as going down rather than up, but hey, i didn't invent the analogy...)

so to answer your question, just relate the business to the appropriate branch level (sub)category

in fact, don't think of the level, just hang the business on the tree wherever it fits

note how easy it is to imagine three levels:

Restaurants
  Gourmet
    French
      Maison Marcel 
    Italian
      Luigi's Trattoria 
  Fast Food
    Burgers, Fries, Obesity
      McDonald's 
      Wendy's 
    Chicken
      KFC

see, here we have three levels -- don't limit yourself by thinking in terms of only two

rudy
http://r937.com/

[webgodjj]

actually, webgodjj's original statement is true -- by definition, a primary key must be unique

your Business_Category has a perfectly good primary key definition, a composite of name and parent

however, this structure implies that the same subcategory can belong to more than one parent, i.e. the pairs {foo,bar} and {foo,qux} are distinct, therefore good primary key values, and yet foo belongs to two parents

that's a sophistication that one doesn't normally find in a category/subcategory structure, which is more commonly implemented as a strict hierarchy, i.e. each subcategory belongs to at most one parent

I'm a bit confused here on your point... do the tables keep the two primary id's or do I drop one? You seem to say both?

Also... good points about the Tree structure...

[r937]

i am sorry, i goofed slightly

my comment about the perfectly good composite primary key was not about the Business_Category table (although it has a composite primary key as well!) but rather about the Category table

the Category table does not have two primary keys, it has one, which happens to be a composite key, made up of two columns

splitting hairs? not at all :-)


as far as the appropriateness of the pair of columns {name,parent} acting as the Category table primary key, this would permit the same category to beloing to two different parents

i wouldn't do it that way myself, but freakysid may have a situation which required it

and no, you don't drop one of the Business_Category columns, they are both vital to the way that table works, as a many-to-many association or intersection or junction or relationship table between the businesses and the categories

rudy

[webgodjj]

Then the quesiton remains.. How would you do it? Can you show me the table format you would use?

[r937]

i would use a strict hierarchy, i.e. each subcategory can belong to only one parent, and i would also use a surrogate key (auto_increment), that way you can have two different subcategories with the same name (example: "Programming" in DMOZ as previously explained)

using a surrogate primary key also means that you don't have the parent link as a varchar (repeating "Restaurants" in all its subcategories)

create table Category
( id integer not null primary key auto_increment
, name varchar(100) not null
, parent integer not null
, foreign key (parent) references Category (id)
)

rudy

[webgodjj]

Rudy, Sorry about the late reply. I was out of town for a few days. I like your approach, however, i don't really understand it fully. Can you give an example of data to be put in this structure and how I would grab this data (sql statement)?

[r937]

sure, using the same example data i gave before:

201 Restaurants   null
204 Gourmet       201
205 French        204
207 Maison Marcel 205 
209 Italian       204
211 Luigi's Tratt 209
215 Fast Food     201
218 Burgers, etc. 215
219 McDonald's    218
221 Wendy's       218
230 Chicken       215
233 KFC           230

as for the sql, there are many ways to approach this

how many levels will you have in the category/subcategory structure? if you can limit it to a fixed maximum, you can use self-joins, otherwise you need a recursive loop driven by your scripting language

rudy

[webgodjj]

Not to many levels... This site is a list of cities. You will be given a chance to search for business in two ways. 1. You will be on a city page, in which, you will get a pull down list of business. 2. You will go to a differnet page on let's say "Restaurants". You can then search via cities.

Now, some restaurants will list one or more "subcatagories" ie. fast food, Itallian, pizza, french.... However, not every (in fact most) of these won't immediately have a "subcategory known. They will just be listed as a Restaurant.

There will be diffent business types than Restaurants... ie Accomodations which will list Hotels, Motels, B&B's...

So, I made a large business table.

DROP_TABLE_ IF_ EXISTS_Business ;
CREATE_TABLE_Business_ (
__ bid_mediumint_unsigned_NOT_NULL_auto_increment ,
__ b_name_varchar (50 )_ NOT_NULL ,
__ b_address_varchar (100 )_ NOT_NULL ,
__ b_city_varchar (30 )_ NOT_NULL ,
__ b_state_varchar (3)_ NOT_NULL ,
__ b_zip_int (12 )_ NOT_NULL ,
__ b_county_varchar (30 )_ NOT_NULL ,
__ b_phone_int (11 )_ NOT_NULL ,
__ b_contact_first_varchar (20 )_ NOT_NULL ,
__ b_contact_last_varchar (30 )_ NOT_NULL ,
__ b_website_varchar (100 )_ NOT_NULL ,
__ b_email_varchar (100 )_ NOT_NULL ,
__ b_image_varchar (100 )_ NOT_NULL ,
__ b_paid_varchar_ (3)_ NOT_NULL ,
__ b_comment_text_NOT_NULL ,
__ b_datemodified_TIMESTAMP (8), 
__ PRIMARY_KEY__ (bid )
);

And I blieve there will be a need for a look up table:

CREATE TABLE Business_Category (
b_id mediumint unsigned NOT NULL,
category varchar(100) NOT NULL,
PRIMARY KEY (b_id, category)
);

to tie into the new category table:

 create table Category 
  ( id integer  not null  primary key  auto_increment
  , name varchar(100) not null
  , parent integer not null
  , foreign key (parent) references Category (id)
  );

Do the tables look right? I think that the City and County should go into the Category tables?

Then how would I look up a restaurnts in a given city... ie find all restautants in Biloxi.

Also, How about finding the listings that are more precise: Find Pizza in Biloxi. Or Find Pizza in all cities...

Then the problem arises is how do I add items to all of these tables (the back end of the site ( and most important, Delete Items)

[r937]

tables look okay, you may have some syntax errors defining them, but you'll figure them out

categories are for business type structure (tree)

city is not a business type, it is an attribute of a business

restaurants in biloxi, hmmm, lemme see...

suppose 1. You will be on a city page which has a dropdown list to choose a business type (category)

select b_name
  from business b
left
  join business_category bc
    on b.b_id = bc.b_id
   and          bc.category = form.category
 where b_city = 'Biloxi'

where form.category is the pk from the dropdown list selected item value

or 2. you're on the "Restaurants" page which has a dropdown list of cities to choose from

select b_name
  from business b
left
  join business_category bc
    on b.b_id = bc.b_id
   and          bc.category = 201
 where b_city = form.city

where 201, say, is the pk of "Restaurants"

you are wise to anticipate these design questions before commencing coding

[webgodjj]

Ok.. this spawns two more questions (always seems to be a few) 1. in the category table: Do business names go in there? Would I make need a lookup table?

Also, I already have a completely filled in Business table. Now I'm making the other tables. How would I go about easily creating the other tables... there are 500 business, entering all of that by hand to the other tables might be hard.

Now let's examin the tables again....

The business tables is pretty straight forward. The Business_Category table and the Category table seem a bit strange. So far, there are no subcategories to add to the restaurants.

Buisnes table
+-----+--------------------------------+------------------------------+------------
| bid | b_name	| b_address			| b_city	| b_state	| b_zip | b_county    | ...
+-----+--------------------------------+------------------------------+------------ ...
|   2 | ARBY'S	| 9326 HIGHWAY 49	| GULFPORT	| MS		| 39503 | HARRISON    | ...

Category table
+-----+-----------------------
|cid |  name		|  parent|
+-----+-----------------------
|1   |  RESTAURANT	| NULL	 |

Business_Category table
+-----+------
|cid |  bid	|
+-----+------
|1   |  2	|

[r937]

you do not need any more tables

a business goes in the business table

a business type goes in the category table

when you say you have already entered 500 businesses, did you enter anything anywhere about which business type each one belonged to, e.g. an old version of the table where this was carried as a text field for each business, perhaps?

if so, you can populate the other tables from that

if not, you will not have to re-enter any business information, but you will have to create 500 rows for the business_category table, after manually creating the contents of the category (business types) table, which, if you haven't done it yet, go do that first

rudy

[webgodjj]

Hmm... I get the idea about populating the tables... no problem there.

Let's take my expample a bit further... Now let's say I make the category table with one entry.. that being RESTAURANT. I then put all 500 Restaurant bid into the Business_Category table with the cid values equal to 1

Now a restaurant gets back to me and says that they server both Itallian and French Cuisine. (side note: there structue for the Categories won't go deeper than 2 deep) Restaurant will be the Category here and all other types will be a subcategory of that.. no subcategories of subcategories...)

Do I modify the tables to look like this then?

Category table
+-----+-----------------------
|cid |  name		|  parent|
+-----+-----------------------
|1   |  RESTAURANT	| NULL	 |
|2	 |  ITALLIAN    | 1      |
|3   |  PIZZA       | 1      |
|4   |  FRENCH      | 1      |

Business_Category table
+-----+------
|cid |  bid	|
+-----+------
|1   |  2	|
|4   |  2   |
|2   |  2   |

[r937]

me, i would make Pizza a subcategory of Italian, you have it under Restaurant, pick whatever makes sense to you

as you point out, when your category structure evolves, you will want to "re-slot" some businesses into better categories or subcategories

regarding your example business_category data --

cid bid
1 2
4 2
2 2

this links business number 2 to categories 1, 4, and 2

i suggest that you not link a business to both a category and one of its subcategories

as for reassigning business to better categories, let's say the business who got back to you was b_id=204 and was categorized under Restaurant (c_id=1), and you decided to reassign it to Italian (c_id=2)

UPDATE business_category
SET c_id=2
WHERE b_id=204

you are picking this up really nicely, but i'm afraid i cannot code the entire application for you

[aspnewbie]

I'm working on developing an administrator module in ASP which will allow for the assignment of multiple categories to certain links. I totally understand the logic behind the many-to-many relationship and the subcategories and have mapped out my database design properly. My problem is in trying to populating the junction table and figuring out how the update will work.

I had wanted to use a dynamically generated multiple select list of categories on the links page, with the categories that the user initially selected to be highlighted in the drop down list. (the categories would be set on a different page). But maybe there is a better way of going about this?

On an update, the linkcategories table would have to be updated (potentially with rows being deleted or added as part of that update.) For example, they might have initially chosen 2 categories and then they went to edit it and selected a third. Or they removed the link from one of the categories.

I would like to do the SQL via a stored procedure. Any suggestions would be great or if you could steer me towards a tutorial that would enlighten me I would be grateful. I've looked in a number of books, but haven't come across anything similare.

r937, can you take pity on a fellow torontonian and steer me in the right direction?

[r937]

stored proc? you're in the wrong forum

fellow torontonian? does this mean you'll buy me beer?

okay, on your admin page, you'll be updating a given link record, so you'll have the linkid primary key for that

then for your dropdown list for the categories, each option should be of the form

<option value="catid">catname</option>

when the form is submitted, the selected options are sent over as a list of catids

okay, now what you do is first delete the junction rows that existed in the table that aren't in the list sent over from the form

delete
from junction
where linkid = linkid
and catid not in ( list of catids )

this has to be the first step, for obvious reasons

second, query the catids that are left over

select catid
from junction
where linkid = linkid

this gives you the ones that existed in the table and the user has also selected them again

now do your asp script magic, and insert all junction rows from the dropdown list that aren't in the list you got from the select

the above procedure prevents unnecessary deletes/re-inserts

you could, i suppose, do the brute force method -- delete all junction rows for the selected linkid, then insert all the new catids

depending on numbers involved, chosing the brute force might be easier, since you won't have that extra select query

helps?

rudy