Solved

Help with handling (merging) 2 records in same the table

Posted on 2013-01-25
13
504 Views
Last Modified: 2013-02-28
Hello,

I have 2 tables, T_PersonHeader and T_PersonDetails

My scenario is, I have 2 entities, Student and Teacher.

For T_PersonHeader, I only store the following: PersonID, PersonType (Student or Teacher), Status (Active/Inactive)

Each entities have their own attributes and all the attributes are stored in T_PersonDetails
When I create a Teacher record and a Student record I will have 2 rows in the T_PersonDetails table.

I have a screen that loads the attributes for Person.
If Student, I should be able to find out the Teacher that the Student links to and vice-versa.

How do I link this at a database level?

I can only think of merge Teacher and Student record into new record (let screen loads from this record). But I want to keep Student record and Teacher record separately, as a Teacher can become Active again.  

I want to know what is the current best database practice for handling this?

Many thanks.

Tri
0
Comment
Question by:trihoang
  • 4
  • 4
  • 3
  • +1
13 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 400 total points
Comment Utility
I'd recommend the following database structure:

tblPeople
---------------
PersonID   (autonumber)
LastName
Firstname
' etc -- other details such as address/phone/email...


tblCourses
---------------
CourseID  (autonumber)
CourseName
TeacherID   <---- this holds the "PersonID" of the teacher from the People table
'etc --- any other course related details such as days/times/location/etc

tblEnrollment
------------------
EnrollmentID (autonumber)
CourseID        <----- This holds the courseID from the Courses table
StudentID      <---- this holds the "PersonID" of the student from the People table
etc -- any other enrollment info such as signup date, drop date, etc...


With that structure you can query your database easily to list all students in a given couurse, all students taught by a given teacher... etc...
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
btw, it's not clear from your question's topic areas (Contact management, Oracle, MS Access) what platform(s) you are working with.  Can you describe this a bit?
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
Your current design may involve lots of coding.
In general teacher is a separate table from students table. Another table is for Courses. Enrollment links the tables together.
Decide on what approach to follow and upload a sample database.
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 100 total points
Comment Utility
Agree with mbizups recommended table structure, and agree with hnasr that I would add a Teachers table to mbizups structure.  tbl_Teachers would hold data that is unique to teachers, as opposed to what would be in tbl_People.

tbl_Teachers:
Teacher_ID (FK)
Dept_ID
Start_Date
End_Date

Note: Teacher Start and End dates might actually belong in a separate table (Teacher_History) which tracks more detailed information including salary, Title, and other fields that might change over the period the individual employment.
0
 

Author Comment

by:trihoang
Comment Utility
Hi mbizup,

I'm using Oracle. From my understanding, I can use put everything into 1 table and use VARCHAR2 for the columns.

Ideally, I only want 2 tables.
The Header table will store the common attributes for both Teacher and Student, and the Details table will store all the attributes.

What is the problem with this design?

I'm thinking in term of maintenance, for example in the future I want to add a new type of person in ie Parent, I don't need to create a new table to hold Parent's unique attributes.
0
 

Author Comment

by:trihoang
Comment Utility
Each record is OK by themselves. Having their own unique attributes (in Details table) and status (in Header table)

However, the issue comes when I want to do an allocation, meaning allocating a student to a teacher.

How do I link the records together in Details table?

A standard way is to merge the 2 records in Details table and create a new record, then delete the 2 old records.

However, the issue is both records can be unpair and pair back (ie Student move on to next year and assign a new Teacher, I will need to unpair from old Teacher and Pair again to new Teacher) so that is why I want to keep the 2 records separately in Details table.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
"A standard way is to merge the 2 records in Details table and create a new record, then delete the 2 old records."

No, this is not a "standard" in relational databases.  As mbizup stated in her original post, the "standard" is to have a third (linking) table, which shows the relationship between teacher and student.

If this is for an school where the students are only associated with one teacher for each year, then you probably don't need a "Courses" table, but probably do need a "Classes" table, where you indicate what the class is, the start and end date of the class, what room the class is in, who the teacher is, and so on.  

Then, your linking table would be tbl_Class_Students.  In this table, you would only need the Class_ID, and Student_ID.  Assigning students to a class, by virtue of the Teacher_ID being stored in the Classes table, associates the student with the teacher.  If there is a possibility that a student could be shifted from one class to another during the period of a class, then you would probably also need Start and End dates in tbl_Class_Students.  These dates would, by default be Null (or the same as the Class Start / End dates), and would only be changed if the student left a class or joined a class in mid semester.
0
 

Author Comment

by:trihoang
Comment Utility
I'm not worry about the Course/Class table.
Right now I'm looking for a way to pair and unpair Student and Teacher entities using only just 2 tables.

Is this possible?
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
That is doable with a 'people' table... and a 'Teaching' table which simply links teachers to there students.  Like the courses table without the course information.

With that structure you can add course info to that table at a later date if needed.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Doable, yes.  Advisable, No.  Just my 2 cents.  ;-)
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
To keep discussion on track, use the info gathered and upload a sample database to show the issue related to the question.
"Help with handling (merging) 2 records in same the table"
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
hnasr,

From his reply,  he is using Oracle, not MS Access.

trihoang,

In fact, if Access is not part of the equation, let me know and I can fix the topic areas.
0
 

Author Comment

by:trihoang
Comment Utility
mbizup,

It is not Access specific.

All,

I will upload a sample by tomorrow.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
GoldMine CRM has a fairly open database architecture , however in this case the email distribution list is stored in a quirky way and does not allow easy access for external use. This article will give details of this and provide a method to extr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now