April 3, 2017 at 12:27 pm
Hi,
Trying to figure out what to do on a new student DB where all the table related to a student will have a unique student ID. Is it redundant to use the Identity column as well? As part of primary key?
CREATE TABLE [dbo].[Students](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SchoolYear] [int] NOT NULL,
[StudentID] [int] NOT NULL,
...
...
CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED
(
[ID] ASC,
[SchoolYear] ASC,
[StudentID] ASC
)
Would the Identity column become unmanageable if I were doing nightly loads of student file?
Thanks in advance.
April 3, 2017 at 12:32 pm
mcampoam - Monday, April 3, 2017 12:27 PMHi,Trying to figure out what to do on a new student DB where all the table related to a student will have a unique student ID. Is it redundant to use the Identity column as well? As part of primary key?
CREATE TABLE [dbo].[Students](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SchoolYear] [int] NOT NULL,
[StudentID] [int] NOT NULL,
...
...
CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED
(
[ID] ASC,
[SchoolYear] ASC,
[StudentID] ASC
)Would the Identity column become unmanageable if I were doing nightly loads of student file?
Thanks in advance.
If the StudentID coming in is unique, yes. Also, you don't want a column named just ID in a table, what type of ID is it?
April 3, 2017 at 12:50 pm
Thank Lynn,
Yes the student id would be unique. Actually one more column would be part of the key "School Id' since a student can have multiple enrollmments.
So this would be the way to go? :
CREATE TABLE [dbo].[Students](
[SchoolYear] [int] NOT NULL,
[StudentID] [int] NOT NULL,
[SchoolID] [int] NOT NULL,
...
...
CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED
(
[SchoolYear] ASC,
[StudentID] ASC,
[SchoolID] ASC
)
The ID I noted above was nothing more than the auto-generated value.
April 3, 2017 at 1:15 pm
Why is school year part of the student table?
What happens when a student is enrolled for 2017, and then enrolls for 2018?
You may want to think about normalization rules.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 3, 2017 at 1:52 pm
mcampoam - Monday, April 3, 2017 12:50 PMThank Lynn,Yes the student id would be unique. Actually one more column would be part of the key "School Id' since a student can have multiple enrollmments.
So this would be the way to go? :
CREATE TABLE [dbo].[Students](
[SchoolYear] [int] NOT NULL,
[StudentID] [int] NOT NULL,
[SchoolID] [int] NOT NULL,
...
...
CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED
(
[SchoolYear] ASC,
[StudentID] ASC,
[SchoolID] ASC
)The ID I noted above was nothing more than the auto-generated value.
SchoolYear should NOT be part of the Students table. It should be part of a separate Enrolments table.
April 3, 2017 at 1:56 pm
Michael L John - Monday, April 3, 2017 1:15 PMWhy is school year part of the student table?
What happens when a student is enrolled for 2017, and then enrolls for 2018?You may want to think about normalization rules.
Yep. In fact, the idea raises a number of other questions about how this was designed. Data about the student that is either identification related or for which there can only ever be one value should be in a Student table, with a StudentID as the primary key. Data about enrollments should be a separate table describing enrollment-related activity, and should have StudentID as a foreign key and EnrollmentID as a primary key. Data reflecting student performance (e.g. grades, homework assignments, etc.) should be in StudentData, with StudentID as a foreign key, and StudentDataID as a primary key. If the design of the database doesn't resemble what I'm describing, it's likely to be flawed in a significant way that will cause pain and suffering on trying to develop good queries for reporting.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 3, 2017 at 2:25 pm
The second question is school id.
Will the student id be the same for all schools?
For example, Michael John is registered in School Id 1 with student id 1, and he is also registered in school id 2 with student id of 100.
Lastly, is this a school assignment, or real work?
If it's a school assignment, I wish someone in academia would come up with a better assignment than "Design a school database"
if it's real work, this wheel has already been invented.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 3, 2017 at 2:34 pm
sgmunson - Monday, April 3, 2017 1:56 PMMichael L John - Monday, April 3, 2017 1:15 PMWhy is school year part of the student table?
What happens when a student is enrolled for 2017, and then enrolls for 2018?You may want to think about normalization rules.
Yep. In fact, the idea raises a number of other questions about how this was designed. Data about the student that is either identification related or for which there can only ever be one value should be in a Student table, with a StudentID as the primary key. Data about enrollments should be a separate table describing enrollment-related activity, and should have StudentID as a foreign key and EnrollmentID as a primary key. Data reflecting student performance (e.g. grades, homework assignments, etc.) should be in StudentData, with StudentID as a foreign key, and StudentDataID as a primary key. If the design of the database doesn't resemble what I'm describing, it's likely to be flawed in a significant way that will cause pain and suffering on trying to develop good queries for reporting.
This, is in fact how I will be designing it. There will be a StudentEnrollments table as well as StudentCourses, StudentGrades, Assessments, etc...
The original question was more in relation to whether using an Identity column was needed or not.
My only thinking on the SchoolYear was that the system will allow selection of a school year to look at prior 'snapshot' of a student's whole picture for that given year (grades, demographics, course, teachers, etc..)
I would think all those entities; Students, Teachers, Courses, etc... should have the school year as part of the key/table. So from an application if a user selects a prior school year it pulls data for how a student 'looked' that year.
April 3, 2017 at 2:39 pm
Michael L John - Monday, April 3, 2017 2:25 PMThe second question is school id.
Will the student id be the same for all schools?
For example, Michael John is registered in School Id 1 with student id 1, and he is also registered in school id 2 with student id of 100.Lastly, is this a school assignment, or real work?
If it's a school assignment, I wish someone in academia would come up with a better assignment than "Design a school database"
if it's real work, this wheel has already been invented.
Student ID will be the same for ALL schools enrolled. A student can have multiple enrollments like online course, home schooled for part of day and still take classes at a brick and mortar.
For work... and yes I'm looking at other systems that we have in house that are vendor products and the school year is prevalent across tables.
thanks
April 3, 2017 at 3:00 pm
mcampoam - Monday, April 3, 2017 2:39 PMMichael L John - Monday, April 3, 2017 2:25 PMThe second question is school id.
Will the student id be the same for all schools?
For example, Michael John is registered in School Id 1 with student id 1, and he is also registered in school id 2 with student id of 100.Lastly, is this a school assignment, or real work?
If it's a school assignment, I wish someone in academia would come up with a better assignment than "Design a school database"
if it's real work, this wheel has already been invented.Student ID will be the same for ALL schools enrolled. A student can have multiple enrollments like online course, home schooled for part of day and still take classes at a brick and mortar.
For work... and yes I'm looking at other systems that we have in house that are vendor products and the school year is prevalent across tables.thanks
Typical, I worked for a public school district. There was no separate enrollment table, that information was in the student table and there were three student ids in the table. An internal student id, a district student id (usually the same as the internal id but this one could be modified), and a state student id.
April 3, 2017 at 3:29 pm
Lynn Pettis - Monday, April 3, 2017 3:00 PMmcampoam - Monday, April 3, 2017 2:39 PMMichael L John - Monday, April 3, 2017 2:25 PMThe second question is school id.
Will the student id be the same for all schools?
For example, Michael John is registered in School Id 1 with student id 1, and he is also registered in school id 2 with student id of 100.Lastly, is this a school assignment, or real work?
If it's a school assignment, I wish someone in academia would come up with a better assignment than "Design a school database"
if it's real work, this wheel has already been invented.Student ID will be the same for ALL schools enrolled. A student can have multiple enrollments like online course, home schooled for part of day and still take classes at a brick and mortar.
For work... and yes I'm looking at other systems that we have in house that are vendor products and the school year is prevalent across tables.thanks
Typical, I worked for a public school district. There was no separate enrollment table, that information was in the student table and there were three student ids in the table. An internal student id, a district student id (usually the same as the internal id but this one could be modified), and a state student id.
This^^
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply