What is the best design for these data base tables? [closed]

Question

I need to find the best solution to make the DB Normalized with large amount of data expected.

My site has a Table Tags (contain key word,id) and also 4 types of data related to this tags table like(articles,resources,jobs,...).

The big question is:- for the relation with tags what best solution for optimazaion & query speed?

1. make a table for each relation like:

   • table articlesToTags(ArticleID,TagID)
   • table jobsToTags(jobid,tagid)
   • etc.

2. or put it all in one table like

   • table tagsrelation(tagid,itemid,itemtype)

I need your help. Please provide me with articles to help me in this design

consider that in future the site can conation new section relate to tag

Thanks

Answer 1

I would go for the normalized version of your schema (which is the table-relation). This type of schemas are very useful for scenarios where the application might grow.

The bad thing of having all the data in just one table is that if you have a bunch of attributes for both relationships, you'll end up with a table with a lot of attributes, which when growing will be slow to query, thus becoming a performance hit of your app.

So, finally the problem is to choose simplicity and quick end against well designed code considering scalability as well.

Hope I can help