MySQL Database - Table to share contents between users

Question

I just wanted to know which one you think it's the best solution to this situation.

I've got an application in which items can be shared. These items, however, can be of different types: photos, posts, even "groups of posts", and so on, so there's a table called 'posts', another called 'media', etc.

The users will be able to see only those items shared with them and those they created. So, what do you think it's better?:

• Make a table called 'shared_items' in which I'd put the Item_ID, User_ID, Item_Type.

• Make a table for each type of item (for example: shared_posts, shared_media, etc.), with the same structure above explained (but without Item_Type).

More solutions are accepted, of course, but these are the only ones that are coming to my mind right now.

If you need a database structure sample just tell, but I think it's not necessary.

Thanks for your time!

Answer 1

It depends on how you are going to query your data and how often. If you need to get all of shared items in one query, single table would be preferable. It will get rid you table joins. As for the database normalization I would say keep your flies separately, soup separately and go for shared media, shared posts, shared anything tables.