Member Avatar for rajesh1158

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??

  • 2 Contributors
  • 5 Replies
  • 193 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by hfx642
Member Avatar for hfx642

Well... What I would do is...
Have a Project Table, a User Table, a Skill Table,
and a separate "link" table, with Project_ID, User_ID, Skill_ID, and Mark.

If you are doing Oracle Forms, you can have a Form for;
a) Projects (Master) listing Users (Detail) and Skills (Detail with Marks)
b) Users (Master) listing Skills (Detail with Marks) and Projects (Detail)
c) Skills (Master) listing Users (Detail with Marks) and Projects (Detail)

Member Avatar for rajesh1158

Hey hfx642,

The 'link' table which u suggested even i had. Read my question properly. I said the table will be very bulky as for each month, around 500,000 records will be inserted. Performance will be an issue.

Edited by rajesh1158 because: n/a
Member Avatar for hfx642

How long does a project remain "current"?
Create an archive table with the same structure as the link table.
Your link table should also have an Insert_Date column.
Create a Before Insert (Statement) Trigger on the link table
to move non-current project records into the archive table.
With all of the IDs indexed, their shouldn't be much of a performance issue.
I've dealt with tables holding 25M-35M records.
(Obviously, they didn't do any archiving!)

Member Avatar for rajesh1158

I have a status column in the project table saying if the project is active or inactive. Actually, the project manager can view only active projects. But this does not mean that we can delete the inactive projects. They need to be maintained for future use(Company policy does not allow to delete any info).

I just want to know that if suppose all projects are active in an organization, will the large amount of records affect performance? I will be using indexes on columns.

Also i want to know, at what time(number of records), performance will be an issue? I mean how many records can a table in Oracle hold without much performance issues?

Member Avatar for hfx642

Great! You can use that status column to move your link records into your archive table. You can even put the trigger on your project table instead of your link table. (After Update, Active to Inactive)

The answer to your last few questions will depend on hardware.
This is usually something developers have NO control over.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.