Table design

Question

Table design

Viewing 4 posts - 31 through 33 (of 33 total)

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

awp (7/20/2009)
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?

If indexed properly, it will perform well. 3 million's actually a fairly small number of rows. I've worked with 400 million

Those 3 million rows would be there regardless of whether the Heading was in the same table as the marks or not. The marks table would still have the 3 million rows.

You can pull the heading out into a separate table if you wish and put a foreign key. Personally, I doubt I would bother doing that.

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 2

In terms of the number of rows it doesn't matter, but by putting the heading in a different table, they only have to fill a heading once and they can select a heading from a drop down list when they give a mark to it. Also you avoid making spelling mistakes in headings; when you make a cross tab reports this should important, isn't it?

A huge thanks to you Gail for all your input on this topic!!!!

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

You can split them out if you want, up to you. If you chose to do so, just replace the AssessmentHeading column with an assessmentHeadingID and have another table like this

CREATE TABLE AssessmentHeadings (

AssessmentHeadingID INT IDENTITY PRIMARY KEY,

AssessmentHeading

--.. other columns if necessary

)

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 4

Thanks Gail, I think I need to add the subjectID (foreign key) to this table, so that the headings can be categorized.