Database Design - Categories Design Best Practice

Databases
1

Hi,

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

Categories
ex:
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…)

Thanks.

2

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?

3

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?

Thanks.

Regards,

Khaled

4

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.

5

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

6

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.