What would be a good schema to classify objects based on a hierarchy

I have a list of videos which I would like to classify in a hierarchy

category (id, name, parent)
video (id, name)
video_category (id, video_id, category_id)

my categories would look like this

/A/B/C/[C1, C2]
/D/E/F/[D1, D2]

So if my video is categorized as follows

v1 categorized as C1
v2 categorized as C2

If I view underneath "C" it should show me both vi开发者_如何转开发deos.

Is this an appropriate design?

---

I think your design is pretty good, with some small comments and one big but.

The small comments:

• video_category doesn't need an id, its columns form a unique composite primary key already. Adding an extra id here allows you to store duplicate data.
• Try to avoid using 'id' as a column; call the id column in the video table 'video_id' so it's easier to remember which columns in other tables you can join it on (this becomes more and more useful the bigger and more complex your schema becomes)

The big but is but MySQL doesn't support connect by prior, which is a problem as this is the most intuitive way to traverse trees like this. There are ways round it, either by implementing a more complicated schema, or by structuring your tree traversal procedures iteratively instead of recursively. You may find the links in the following useful:

http://forums.mysql.com/read.php?98,38047,38089#msg-38089