August 22, 2008 at 7:24 am
I am working on designing data model for education dept. we collect data using some s/w & store in it DB. All sort of information is collected & there are different tables for storing them. Each table along with information, contains fields which are mandatory for collection of data like term, area code etc.
presently, its a sort of staging DB. I am working on creating relational model.
Some tables are:
student -- Presently, this table contains information about student: Id,name, gender, address, admission details, term and many more items
teacher -- This contains similar information but relating to Teacher.
course -- Courses offered
calendar
Discipline
Enrollment
...
I am thinking of breaking student table into smaller tables as a table for address, admission details. Data fields which are mandatory for data collection should form composite PK.
The Pk would consist of year, district code,schoolcode, term id. This Pk would be in Student info table and would act as FK for other tables like student address, student admission details etc.
Next, I would adopt similar method in creating other tables from teacher table.
Any suggestions?
Thanks
August 22, 2008 at 8:43 am
In the relational model your addresses should be a separate table as should admission details. Without knowing more I'd go with:
Teachers,
Addresses,
Admission_Details
Are you going to have multiple addresses per student or teacher?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 22, 2008 at 9:35 am
Thanks for your inputs. I appreciate your time & thoughts
Well, not sure about the multiple address. need to research on that aspect.This is going to be first draft...
So, trying to get a rough model.. to get some idea. Then, can proceed from there on.
Presently,I am trying to create separate tables for :
Student information(name, Id, gender, language spoken, Ethnicity,birth country)
Student Address(city, zipcode, state etc)
Student School(enter date, exit date, related stuff)
please advise,
Thanks
August 25, 2008 at 6:18 am
Hi Jack,
Multiple addresses exists for single student. So, basically it is One to Many relationship between studentId and Address.
But then so....?
I didn't get implications by such relation
thanks
August 25, 2008 at 6:27 am
In this case you have 2 options:
1. Storing student_id in the student addresses table will allow for multiple addresses per student.
2. Have a Students table, and Addresses table (can be used for any address), and a Student_Addresses table as a "glue" table with student_Id and address_Id. This allows for each student to have multiple addresses and each address to apply to multiple students (many to many relationship). Thus, if you have siblings in the school then you enter the address one time and assign it to both students.
I typically use option 2 because it offers more flexibility albeit with added complexity as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 25, 2008 at 7:01 am
I used to work for a software company for higher education products - student info, admission, registration, financial aid... for colleges.
What kind of system are you designing - just the student info?
All the modules can get into very complex, even student information. We have to store the student info, parent info, transcripts, tuition info.
August 26, 2008 at 7:22 am
Jack,
I also feel option 2 is better. It provides flexibility. thanks
But before that, I am trying to resolve the issue of MIGHTY PKs. I feel resolving this issue can help me in establishing RI & can make life simpler 😉
Original table has composite PK. Now, I think one of the entity ,probably, 'student information' need to have the Composite Pk as it is the only way of uniquely identifying rows. Then May be, I can introduce an identity column here which could help in joining it to other entities.
Do you have any ideas/suggestions for me?
following is PK compose of:
A,B,C,D,ID,Link,Indicator
A,B,C,D are mandatory --necessary for data collection- identify term.
ID- sutdent's ID but can be null
'link' - used in linking with other related tables. It can'e be null in child tables
'indicator' - relates information of student w.r.t school -activity status
August 26, 2008 at 7:23 am
Loner,
Working for an education Dept, I have to design a data model for a DB containing information about students, teachers, courses, accomodation, discipline, attendance, schedule, testscores, Parent/Gaurdian information etc.
Here, the main table is Students table and most of the tables relate to it.So, I am starting with analyzing this table. Later, I will move to other tables.
This table contains lots of data fields. And as I mentioned earlier, I divided it into 3 entities:
One for Student Information
second for Student Address
Third for information pertaining to School- schoolcode, enter date,exit date, no show reason etc
Now,for each table/entity I need to define PKs. The original table has 7 data fields forming composite PK. Something like following:
A,B,C,D,ID,Link,Indicator
Here A,B,C,D are mandatory ,can't be NULL. However, other columns can be.
data field -'link' is used in linking with other related tables. It can'e be null in child tables
indicator - relates information of student w.r.t school -activity status
ID- student's ID but can be null
BTW, this data is stored using an s/w which is used at local schools & there many fields aren't mandatory. For e.g: SSN is not necessary. Students also have District level Id, Statelevel ID but these can be null as well.
So, here I am..
I have this one big table with a composite PK & containing all the information. I want to break it into 3 smaller tables & is trying to figure out PK -FK issue
Please Advise,
I'll really appreciate.
Thanks
August 26, 2008 at 12:16 pm
I agree that the address table should be separated from the student table.
STUDENT
studentid (PK)
Studentname
StudentAddress
addressid (PK)
Addresstype
studentid (FK to STUDENT)
Address
city
state
zip
If you want to normalize it, you can create a table for addresstype and put addresstypeid in the studentaddress table and make an foreign key to AddressType table.
AddressType
AddressTypeid (PK)
AddressType
Description
TEST
Testid (pk)
TestName
Description
STUDENTTEST
Studentid
testid
TestDate
Score
(composite key = studentid, testid, testdate)
COURSE
Courseid
Coursename
CourseDescription
Faculty
Facultyid
FacultyName
Department
ClassSchedule
ClassScheduleid
Courseid
Facultyid
Semester
Classtime
Room
StudentClass
Studentid
ClassScheduleid
I like to design.;)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply