Hi folks. I'm working on a PHP/MySql website that will let users browse a database of Transformers toys (you know, the robots that turn into cars).

Okay, so I start by setting up a database with a field for the NAME of each toy. Good so far.

Over the years, many series of toys have come and gone, ie:

Generation 1
Generation 2
Energon
Armada
Cybertron

Okay, so I add a field to my database for SERIES.

Now, here's where it starts to get tricky. Each series of toys in the Transformers line further divides the toys into one or more sub-categories.

For example, the Cybertron toys fall into the following sub-categories:
Scout
Deluxe
Voyager
Ultra
Supreme

However, the Energon toys fall into sub-categories with completely different names. Don't know 'em offhand, but let's say they are:
Basic
Mega
Ultimate

So what do I do? Do I add an enum field to my database that has values for ALL possible sub-categories?
enum(Scout, Deluxe, Voyager, Ultra, Supreme, Basic, Mega, Ultimate)

Or do I have an enum field with Cybertron values and another with Energon values?
enum(Scout, Deluxe, Voyager, Ultra, Supreme)
enum(basic, Mega, Ultimate)

I don't like the idea of having a giant enum assigned to a toy with dozens of inappropriate values mixed in with the appropriate ones, nor do I like the idea of having lots of additional fields that are n/a for certain toys.

In C++, this wouldn't be a problem. I would have a base class called Toy, with a member variable called m_series. I would then just derive classes off of Toy for Cybertron and Energon, and set up virtual functions for accessing sub-categories, and return the proper sub-category names.

I'm probably missing something extremly obvious and simple here. I am still a newbie here, after all--any suggestions?

Many thanks...
-Max

step #1: create a table for series/categories, and use the adjacency model to create categories within series

see Managing Hierarchical Data in MySQL

step #2: create a foreign key in your toy table to reference the appropriate category for each toy

tip: stay far, far away from the evil ENUM

Wonderful--that's just the information that I was looking for. Thanks much, noble sir.

-Max