I am fresher in Database Design

I want to Design a Database for an online Auditing Tool...for Auditor and Project Managers..

so far I have the following tables...

Employee (EmpID , EmpName,EmpLname ,Phone)

Administrator( AdminID ,EmpID, AdminEmail)

Company(CompanyID, CompanyName,ProjectID)

Auditor( AuditorID, Name, LastName)

Questions(QuestionID, Question)


User One Auditor:

will asses the project ussing the questions in the database and give a score for the project.

User Two Manager

Will view the Overall score for a particular Company and a Score a Single Project under that Company(From a list Box)

I'm having a hard time in creating the relationships between the(Company and Score..Project and Score..Score and Questions)

The Questions are under two sections and there should be score for that particular section and overall score for both sections.

Any Help will be much Appreciated..

Recommended Answers

All 2 Replies

Company and Project should be parent/child entities. That way you can independently relate them to Results.
Question should have a SectionNumber attribute so you can differentiate whether to relate it to a Company or a Project.
You should not store an "overall" score. Frankly, you shouldn't even keep a "Section" score. Just sum the low-level question scores at run-time for each section either in your executable code or in a stored procedure, depending on how you want to implement. If performance is bad, then you can think about storing the calculated values.
You may also wish to consider whether or not a given company or project can be given the questionnaire multiple times (maybe on different dates?). That will require you to have additional attributes for when the questionnaire was administered.
Hope this helps!

commented: that sounds good. +13

Thanks for the reply..a company may have multiple projects running concurrently I will have consult with my stake holder about whether a particular project may be audited multiple times..I have attached an image of the tables i have have Designed if anything jumps out would you kindly notify me..

thank you

Be a part of the DaniWeb community

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