Hi all,

I have a table called students, with the following structure:

Students:
- StudentID
- InstitutionID
- AccountID
- FirstName
- LastName
- Phone
- Mobile
- Email
- StreetAddress
- Suburb
- PostCode
- StateID

I have different types of students that will be stored in the db table:
Mature age students are required to provide all info in the respective
table.
Under age students are only required to provide the following info:

- InstitutionID
- AccountID
- FirstName
- LastName
- Email

For reasons of PERFORMANCE & GOOD DATABASE LOGICAL DESIGN,
should i create two db tables to store this info:

Or is the amount of redundancy acceptible, if i use the one table to store
both types of students.
Is there another option??? Maybe involving a VIEW?

Would appreciate any insight into this !!!!

Cheers,
Adam

Going with just one table, you will not only have a lot of NULLs, but you
also have other limitations, like a student can have only one email address
(the student may actually have more than one, but you're table can't hold it).

Here's a structure you might consider, in quasi-sql

Students (
  StudentID,
  InstitutionID,
  AccountID,
  FirstName,
  LastName
  PRIMARY KEY (StudentID)
)

StudentPhoneNumbers (
  StudentID
  PhoneNumber
  PhoneType --consider a bit flag for land line or mobile
  PRIMARY KEY (StudentID, PhoneNumber)
  FOREIGN KEY (StudentID) REFERENCES Students (StudentID)
)

StudentEmailAddresses (
  StudentID
  EmailAddress
  PRIMARY KEY (StudentID, EmailAddress)
  FOREIGN KEY (StudentID) REFERENCES Students (StudentID)
)

StudentMailingAddress (
  StudentID
  StreetAddress
  Suburb
  PostCode
  StateID
  PRIMARY KEY (StudentID,StreetAddress,PostCode)
  FOREIGN KEY (StudentID) REFERENCES Students (StudentID)
)
Show quoteHide quote

On Sun, 15 Jan 2006 09:20:37 +1000, Adam J Knight wrote:

(snip)
Hi Adam,

In such cases, you'll often want to set up one table for the information
required for all students (Students), and a second table for the
information that is only entered for a subset of the students
(MatureStudents). If there are also columns that only apply to underage
students, you'll have a third table (UnderageStudents) for that info.

Both "subtables" have StudentID as both Primary Key *AND* Foreign Key
into the main Students table.

Note that this is generic advice, not based on the specific columns
mentioned in your message. Mark already addressed those.

Outer Join and WHERE clause strange behavior
MINUS operation between table date ranges, is my algorithm sound?
generic audit trail best practice questions
append a string to all the values in a column SQL
Extracting from linked tables into pivot