I have to design a database where one requirement says "For each project, for each user, for each skill, store the marks gained".
Initially, i had a table where each skill was a column(proj_id, user_id, skill1, skill2, ......., skill_N)
I felt this was a bad design because the number of skills is not known(few may be added in future) and also if there were 100 skills but a user had only 5 skills, the rest 95 columns will be NULL.
Now, i have modified the design as (proj_id, user_id, skill_id, marks). The problem with this is there will be many records. Suppose there are 1000 projects, 100 users per project and average of 5 skills per user in a project, then number of records = 1000 x 100 x 5 = 500,000
There is a similar table where such data needs to be stored monthly where the number of records may keep adding(500,000 then 1,000,000 then 1,500,000.....so on)
My team members suggested i use separate table for each project but then i felt this also bad design because the number of tables will be more and also if a table structure needs to be modified, all those tables need to be modified(because they all will have same structure)
can someone suggest how can i proceed??