Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2006
    Posts
    2

    Unanswered: Database Design Question

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2006
    Posts
    2

    Perfect!

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

    -Max

    Quote Originally Posted by r937
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •