February 24, 2016 at 10:29 am
hlsc1983 (2/24/2016)
k if i insert a gradetypeID column in the table, that means there will be four rows in the table if the subject has all four possible grade types. one row each for theory progressive, theory end term, practical progressive, practical end term eachwill it not make the table very big size? and will it affect the performance for eg: take more time while searching all the grades in four years scored by one student?
Yes it would mean 4 rows for a single student with all 4 grade types. With indexing this will be not an issue at all. I assume by size you mean the number of rows because the actual storage size is not a lot bigger when normalized. To be honest the query for finding all grades in 4 years for a single student isn't going to be noticeable unless you have billions of rows. But if you have that much data the denormalized version would be noticeable slower too. Don't get stuck in the mindset of premature optimization. That is the practice of making non-standard design decisions to deal with a performance problem that doesn't yet exist. Properly normalizing your tables will (almost) always be the best path to take. I say almost because nothing in sql server is "always". There are exceptions to everything because...."it depends". 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 24, 2016 at 10:52 am
k regarding foreign keys, i have understood it this way. correct my mistakes.
Subjects table, Students table and Gradetype table will have one to many relationship with Marks table.
so Marks table will be like this:
student_id int (foreign key) ,
subject_id int (foreign key),
gradetypeID int(foreign key)
marks int,
oddeven bit,
reg_comp bit,
exam_year bit,
February 24, 2016 at 10:58 am
hlsc1983 (2/24/2016)
k regarding foreign keys, i have understood it this way. correct my mistakes.Subjects table, Students table and Gradetype table will have one to many relationship with Marks table.
Sort of. This works but using exam_year is problematic. What happens if the same student takes the same class back to back semesters? What do you put for the year if they take a class in the Spring and again in the fall??
so Marks table will be like this:
student_id int (foreign key) ,
subject_id int (foreign key),
gradetypeID int(foreign key)
marks int,
oddeven bit,
reg_comp bit,
exam_year bit,
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 24, 2016 at 11:08 am
i meant to write like this: exam_year int,
The reason why i am using exam_year is because the marksheet that the system must finally print reads something like this:
"The following marks are obtained by (studentname) in the (semester name) semester in (branch name like computer science engineering) held during (month and year)
"
therefore i thought of pulling the values from the Marks table.
and yes i have to add another column for month right?
February 24, 2016 at 12:07 pm
hlsc1983 (2/24/2016)
i meant to write like this: exam_year int,The reason why i am using exam_year is because the marksheet that the system must finally print reads something like this:
"The following marks are obtained by (studentname) in the (semester name) semester in (branch name like computer science engineering) held during (month and year)
"
therefore i thought of pulling the values from the Marks table.
and yes i have to add another column for month right?
Instead of month and year columns why not just a single column that contains date? Or even better have a semesters table with things like start and end dates. Then have the SemesterID as a column in the marks table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 24, 2016 at 12:16 pm
k... i will design the database as per the suggestions here and get back soon for more feedback. do i have to start a new thread or stick to this ? sometimes the threads just seem to inactive / dead due to no response.
February 24, 2016 at 12:26 pm
hlsc1983 (2/24/2016)
k... i will design the database as per the suggestions here and get back soon for more feedback. do i have to start a new thread or stick to this ? sometimes the threads just seem to inactive / dead due to no response.
Probably best to stick to this one. Threads on this site never deactivate. I (and anyone else who responded or subscribed) will get an email anytime there is a new response posted.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 24, 2016 at 12:31 pm
hlsc1983 (2/24/2016)
i meant to write like this: exam_year int,The reason why i am using exam_year is because the marksheet that the system must finally print reads something like this:
"The following marks are obtained by (studentname) in the (semester name) semester in (branch name like computer science engineering) held during (month and year)
"
therefore i thought of pulling the values from the Marks table.
and yes i have to add another column for month right?
You are very right to use the current inputs and outputs of the application/system to help determine what data to collect. But don't allow them to control how the data is collected or displayed.
What is relevant for grades is semester and school year only. The date can be derived -- looked up at run time -- from that info. While you could copy the year from the semester table to the grade table, it's a very bad idea. That's too much denormalization for not a good enough reason.
You're also handicapped in what you're doing because you skipped the logical design phase and went straight to the physical design phase. If you're experienced in design, that's ok for very simple projects, but otherwise you should do a logical design first. After gaining experience in the ld process, a system this straightforward wouldn't take more than 2-3 days. For now, I understand that you prefer to skip this process, but you should look into for future projects.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply