Database Design - Categories Design Best Practice


I have a few tables: videos/photos/stories/pages and each of which contains a field which is category_id

I’m thinking and since each of them has a category_id to design a single table categories

id || title || type
1 album1 1 [photos]
2 fashion 2 [stories]
3 about_pages 3 [pages]
4 album2 1[photos]

Do you consider this database design a correct design with the type field? Or should each table has its own category table (ex:pages_categories, videos_categories, etc…)


Why do you have different tables for videos/photos/stories/pages?

Looking at your categories I wonder… ‘fashion’ is a category, but ‘album1’ and ‘album2’ seem album names to me, not categories?

I thought since the albums have a similar structure to categories …

category_id > album_id
category_title > album_title

What do you think ?

so what you suggest is : designing a table for each category (ex : stories_categories, pages_categories, photos_categories , videos_categories … ) instead of having them in a single “categories” table?




If items can be groups together, then using a separate table for that grouping is perfectly valid - that’s what normalization is.

What you call the table doesn’t really matter. As long as it makes sense to you if you have to revisit it six months down the line.

Dave but in this case … the same database field is got referenced in multiple tables as a foreign key… Is that valid as well?

Yup. That’s fine. There doesn’t have to be a 1:1 relationship between tables. You can have as many tables reference one specific table as you want.