Will there always be exactly one level of subcategory? The answer may be "yes" right now, but if you design it that way, it could be a major headache down the road when they inevitably say, "gee, it would be REALLY nice if I had a second level of sub categories..."
I started by thinking of your categories table with a "parent" row that points to another category. Main categories would have a parent of "0".
So in other words, you would have:
ID, NAME, PARENT
1, New Equipment, 0
2, Trucks, 1
3, Mixers, 1
4, Dump Trucks, 2
5, Pickup Trucks, 2
But then you might run into the problem down the road of having to add something like this:
6, Used Equipment, 0
7, Trucks, 6
etc.
Now you have two Trucks categories (sub-categories, actually), which isn't exactly normalized. Maybe what you could do is have a "category" table, and another table that links the categories together in a parent-child way. So you would have:
ID, NAME
1, New Equipment
2, Trucks
3, Mixers
4, Dump Trucks
5, Pickup Trucks
6, Used Equipment
...
... and ...
CATEGORY, PARENTCATEGORY
1, 0
2, 1
3, 1
4, 2
5, 2
6, 0
2, 6
...
That way, you can categorize things in the category-link table, and you could search on all "Truck"s, and even though you have trucks in two categories, they would show up.
Or maybe you don't listen to me at all! 🙂 I've never developed anything quite like that, but that's how I would start it. Hopefully that helps you a little bit.
