Table design

Question

Table design

Viewing 15 posts - 16 through 30 (of 33 total)

You must be logged in to reply to this topic. Login to reply

awp SSChasing Mays Points: 613 More actions · Answer 1

These data comes from an assessment, a grade awarded for a task; there may be around 10 tasks in a term and these need to saved each term. people should be able to fefer old term data at any time.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

If that's the case, this design may work better. I'd still like more info to be sure.

CREATE TABLE StudentAssessments (

StudentID int, -- foreign key to student table

TeacherID int, -- foreign key to teacher table

AssessementDate DATETIME,

AssessmentType VARCHAR(20), -- Assignment or Task

Mark tinyint,

Comments VARCHAR(MAX)

-- other columns that pertain to all assessments as necessary

)

With that, it doesn't matter if there's 1, 10 or 200 assessments per term. Sure, it'll need a crosstab for the report. Most good reporting tools can do that in the report layout. For the ones that can't, see the link Jeff posted.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

awp (7/18/2009)
These data comes from an assessment, a grade awarded for a task; there may be around 10 tasks in a term and these need to saved each term. people should be able to fefer old term data at any time.

Then you don't need the table to be designed as you have it which is inflexible and requires the addition of new columns when the tasks change. Please read the following article...

http://www.sqlservercentral.com/articles/cross+tab/65048/

... the same principles apply here. Your "table" should exist only as a set of normalized tables and the reporting should be done using dynamic SQL. And, yes, it's very, very fast. I built a reporting system for an IVR that would take more than 14 million calls a year. The time to produce a monthly report by day and hour of day for 9 different row types would usually take something in the area of 3-4 seconds. The underlying tables weren't complicated either.

Just concentrate on storing the data in a normalized, easy to maintain fashion... the reporting will be the easy part.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

And, yes... Gail has absolutely the right idea for the main table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 5

Gail's design shows why it's better for you to give us the information about what the entity is, and what the data is represents. A good designer will consider that and determine when you should build a design like this one.

I like Gail's design as well.

awp SSChasing Mays Points: 613 More actions · Answer 6

Thank you all guys, you have been very helpful and I am very grateful for everyone who contributed to this topic. I thought more or less same way as Gail’s design when I started to work with this system, but the system have been already designed and have been running for some time and now I need to make changes to the system.

I have some experience in creating cross-tab reports, and I am thankful to Jeff for his article on this issue, it was really informative.

Currently the system is designed as follows:

Report Table {

reportID [int] IDENTITY (1, 1) NOT NULL ,

dat_repDate [datetime] NULL ,

Unique Person Number , ---- foreign key to student table

periodID ---- foreign key to Period table

teacherID ----- foreign key to teacher table

subjectID ----- foreign key to subject table

assessment1 -----mark awarded for an assesssment

assessment2

assessment3

…………….4,…..

Comments

….other data…

}

As you can guess these assessments (1,2 3, etc) are different for different subjects, so these assessment headings are stored in different table as follows, so when a report created or generated the appropriate heading are chosen from the following table.

Table subjectDetails {

subjectID -----unique ID of a subject

headingAssessment1 ------heading of assessment1

headingAssessment2

headingAssessment3

headingAssessment4

…………

}

Downside of this design is, when you want to add more assessments to report you need to add columns to both tables, I think Gail’s design suits much better for this purpose, but to implement this, I need to rebuild the whole thing again.

Vinoo Say Hey Kid Points: 670 More actions · Answer 7

GilaMonster (7/18/2009)
Did you change the names to obfuscate things, or are the columns really called that?

How does that matter to solve the problem? you can have any name for your column. Right?

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

Vinoo (7/19/2009)
GilaMonster (7/18/2009)
Did you change the names to obfuscate things, or are the columns really called that?
How does that matter to solve the problem? you can have any name for your column. Right?

Please take no offense to that. I felt the same way because the column names didn't help in understanding the problem. Some folks change column names to "protect the data" on the forum. In this case, it's just another reason to normalize the tables with names that provide some understanding to someone in the future who may not be familiar with the system at all.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

awp SSChasing Mays Points: 613 More actions · Answer 9

sorry for any confusions that I have caused regarding column names.

One more problem that I have with Gail's design is some of the report-data such as 'comments' is one per teacher/student/subject/period (it is not for every assessments), so if I have to implement Gail's design I have to have another table.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 10

If it's possible to have 1 comment per assessment, or not, you can easily get away with not having a comment table. If you can have more than one comment per assessment, then, yes, you'll need a comment table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

awp SSChasing Mays Points: 613 More actions · Answer 11

Comments is one per teacher/student/subject/period, but you have many assessments as you like per teacher/student/subject/period. So when a report is created at the end, you have all the assesment a student had for that subject and the comments for that subject.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Vinoo (7/19/2009)
GilaMonster (7/18/2009)
Did you change the names to obfuscate things, or are the columns really called that?
How does that matter to solve the problem? you can have any name for your column. Right?

You can, but it's awfully hard to recommend a table design when the column names aren't clear. Also, I have seen designs with exactly those types of names, usually as an attempt to avoid having to do database design. When people really do have a table with columns named String1, String2, ... Int1, Int2... then my advice is to scrap the design and do over from scratch as that 'generic' design usually performs terribly and quite often has very bad data in it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

awp (7/19/2009)
I think Gail’s design suits much better for this purpose, but to implement this, I need to rebuild the whole thing again.

Yeah, that is the downside, but at least it just has to be done once and after that you won't need to add columns when no of assignments increase.

How does this look?

CREATE TABLE StudentAssessmentPeriods (

AssessmentPeriodID INT IDENTITY PRIMARY KEY,

dat_repDate [datetime] NULL ,

Unique Person Number , ---- foreign key to student table

periodID, ---- foreign key to Period table

teacherID, ----- foreign key to teacher table

subjectID, ----- foreign key to subject table

Comments VARCHAR(MAX)

);

CREATE TABLE StudentAssessmentMarks (

AssessmentMarkID INT IDENTITY PRIMARY KEY,

AssessmentPeriodID INT, -- foreign key to the StudentAssessmentPeriods table

AssessmentHeading,

AssessmentType -- Assignment, test, etc

AssessmentDate,

AssessmentMark

)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

awp SSChasing Mays Points: 613 More actions · Answer 14

Thanks Gail, this will be a perfect design for our system. Thanks again to everyone.

awp SSChasing Mays Points: 613 More actions · Answer 15

Hi Gail,

A small problem we have with your design is, say for an instance a teacher teaches a subject for hundred kids and she has to fill in the same 'Assessment heading' hundred time when a mark is given for that assessment.

One more thing, I calculated roughly around 600000 records mey be created in the 'StudentAssessmentMarks' table, that is about 3 million records in 5 years time. I am wondering after a how many records a table will perform poorly?

Hopefully this is the last question on this topic,

Huge thanks again.