Relationships Question

[VickAgnes]

Hi,

I have a database that has around 12 tables. All these tables have the same structure (same fields) ... its actually an academic database with a table containing student info ... one for every semester ... What is the relationship that I should use to define these tables??

- Vick

 

[rodrunner79]

If they all have the same field and same data with the exception of semester. Why don't you just create a master table (same exact fields like your current tables) and add an extra field called semester and append all records to it and classify them by semester. And then create a second table called tblSemesters and relate the two tables using a one-to-many relation.

-=True wisdom comes from knowing you know nothing.=-

 

[VickAgnes]

well they don't have the same data ... just the same fields ... so i have a table by the name of the semester having the same structure as the previous semester table but different data (since each semester, different people get admitted ... unless of course a previous semester guy doesn't attend and decides to reapply later ... that's the only case when two tables might have the same row....) ....

 

[rodrunner79]

Oh ok, my apology for misterpretation. But you can still take my suggestion on my previous post and add a unique ID (preferably AutoNumber field) on the master table.

-=True wisdom comes from knowing you know nothing.=-

 

[lespaul]

what are you going to do when someone asks for a report that covers multiple semesters? You are going to get a REALLY ugly UNION query that gets all the information from all the different tables.

If they all have the same data, they should all be in a single table. Have you read the fundamentals document listed below?

I would expect that you would have a SINGLE student table, an Enrollment Table that contains the StudentID and the Semester they were enrolled.

STUDENTS
StudentID (PK)
Name
Address
(any other information about the STUDENT)

ENROLLMENT
StudentID
Semester (composite PK of StudentID and semester)

now each student can be enrolled multiple times, but you only have a single table to report from.





Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[fneily]

Sorry to pile on VicAgnes, but when Access people see a database structure like yours, it drives them crazy. Whoever created that database basically threw out the window all the basic and essential protocols of a relational database. I created a database for some middle schools in my area and the main student table looks something like this:
ID Term LastName FirstName ClassPeriods ClassSubject ClassTeacher ClassTime ClassRoom IntermGrade1 2 3 4 ExamGrade FinalGrade CreditEarned
And some other supporting tables for medical, buses, discipline, withdrawal, special ed. etc.
Is there anyway you can just start over and ,after studing normalization and relationships of Access, build a new and correct database?

 

[VickAgnes]

I see and understand the point that all you enlightened people are trying to make ...
It's just that this is the way that it's being done historically ... just data entry ... no forms, no reports, no queries ... just basically data being stored and using mail-merge in word to print out simple documents ... now, the powers that be have realised that access can be more than just a data store and they want it to print them elegant reports and stuff but they would prefer that the structure not be changed ...

but let me see if i can make them see what you're trying to make me see ... heh

thanks for all the suggestions and i would welcome any more suggestions...

 

[lespaul]

Now that they have seen the light, they need to be guided in the right direction. They want elegant reports, great, what do they want on them? Determining what the user wants to report is critical in designing a structure that will collect the required information for the reporting. What you want to get out of a system determines what needs to be input into it.

Let them know that by requiring you to use the existing structure, you are being limited in what information can be extracted. Assure them that the existing data will still be available, but future data can be much more specific to their requirements.

Read up on normalization and when you've got a design, post it along with a short description of what the database is going to be "doing" and we can help you improve the structure and end up with a system that is easily maintained and modifiable in the future with minimal changes.

Leslie

 

[VickAgnes]

well ... i finally got the go ahead! thanks for all the advice! So from many different tables for many different semesters I went to one single table for all students with an additional column for describing the semester ... I also made the database first normal by creating a different table for advisor info (in the original table(s), the advisor name and email were listed in one column) and third normal by separating academic and personal information ... my question is can i use the same PK (either ID or a combination of first, last and middle name ?)in both personal and academic tables with one of them being the FK to access the other table??

 

[lespaul]

Have you read the link below?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design