Which data model to use to represent nested categories in a small SQL database?

Question

I am writing a small web site for work where employees can consult video capsules on how to use the different programs that we use. I was asked to organize the videos by categories and the categories need to be arbitrarily nested.

I've been looking into two ways to model this: nested sets and adjacency lists. They both seem to have their ups and downs, and I was wondering if somebody could help me choose the model that would best fit my usage scenario.

• I want to display the categories in a menu along with the number of videos under that category
• The site will be updated once a week, if that. The majority of the database activity is going to be reading.
• The site will not be heavily visited. Never more than 2-3 users at once I'd expect.
• Categories are likely going to be established when we build the site and rarely (if ever) touched afterwards. Maybe some categories will be added, but I don't expected categories to be deleted or moved around in the tree.
• This is a summer job, so I expect that somebody else will maintain the site after I'm gone.

Thank you for you help.

Answer 1

category
--------
category_id
name

category_nesting
----------------
parent_category_id
child_category_id

video
-------
video_id
more_stuff

video_category
--------------
video_id
category_id

Answer 2

If it's a small lightly used database that's unlikely to see a lot of change....I wouldn't sweat the issue. Don't spend a lot of time thinking on it: just do it in the simplest, most straightforward, easiest to understand/implement manner.

Git'er done, as they say.