Logical Design!

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,
AdamGoing 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)
)
--
"Adam J Knight" wrote:

> 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
>
>|||On Sun, 15 Jan 2006 09:20:37 +1000, Adam J Knight wrote:
(snip)
>Mature age students are required to provide all info in the respective
>table.
>Under age students are only required to provide the following info:
(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.
Hugo Kornelis, SQL Server MVP