Data normalization

enrique_aeo
enrique_aeo used Ask the Experts™
on
I have a question
I am currently a reporting system, the function of this system is to control the printing of certificates, that is, if you print one or more times, I need to get that control.
To make this system need two tables: The table has 40 field course and the student table has 30 fields. To print the certificate from the table need 12 field course and the student table 13 fields. I create my own tables, but I want to know whether it is necessary to bring those 25 fields to my tables, for example: One thing that I bring to my system tables is: names, but that figure is a master table, my question is if I should bring me to my system
1. code and the names (with this option I am repeating data)
or only
2. code and then refer to the master table (with this option do not repeat)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Use a view that references the master tables producing a data set that has just the fields you need for your report but does not duplicate data. The view only exists while you run your report and is regenerated each time you need it. You can independantly write back any tracking data to the main tables if you need to.
Regards
Alistair

Author

Commented:
The relationship between the course and students is one to many. The process is as follows
1. Enter the course code
2. It shows the course details
3. Data are samples of students
4. Press a button to print the certificates for all students
My question is whether the steps 1,2 and 3 can be made with a view?

Commented:
In the table tblStudents, you need a unique StudentID for each student named in the table.  Thereafter, throughtout all other tables and queries in your application, you need only refer to the StudentID in tblStudents to fetch any other paramteter about that student you have recorded in tblStudents.
Question:
Is it really a straight one:many between course and students? Or can a student also take many courses? If the latter you actually need an extra table to break the many:many and that complicates the view slightly but a view is still the solution. Your design would have:
tblCourse(CourseID, CourseName, CourseField1, CourseField2...)
tblStudent(StudentID, FamilyName, FirstName, DoB, StudentField1, StudentField2...)
tblCourseStudent(CourseID, StudentID, PerformanceMetric1, PerformanceMetric2...)
You would use the data in tblCourse for the selection dropdown and to populate course information on a form.
You would use a view on tblCourseStudent with WHERE CourseID=SelectedCourseID and JOINed to tblStudent to retrieve the student/performance data needed for each certificate.
You don't mention how you are creating the actual certificates; sql reporting services, or a custom front end. I often use MS Access for reporting front-ends via a linked tables - great reporting tools and easy to output things as PDF.
Regards
Alistair

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial