August 12, 2015 at 4:03 am
Hi ,
We are handling University automation project.Here one discussion is going on regarding design the database.
All the student marks we are storing in database in one column(flat) like (88.5,55.5,64.5).But in my application some reports displaying rounded mars like (89,56,65).
Here some people argued we are require store the both column like correct mark and rounded mark. Some people one column enough ,Rounded mark not required in database. While displaying a report convert the mark in rounded format.
In proper database design which one is correct.Please share with me your suggestion.
August 12, 2015 at 5:51 am
I'd say neither.
Instead, I'd suggest normalizing the scores/marks out to their own table. Then, store them as the correct decimal values. Rounding and prettying can occur on the application/reporting side of things. The data should be clear and consistent.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 12, 2015 at 9:22 am
I agree with Grant. Neither option is the best option. A separate Marks/grades table that stores each grade as a new record with proper data typing.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 12, 2015 at 10:23 am
I agree on normalizing. In other words, store each mark as a separate row.
Student Mark
--------- ------
Bill 88.5
Bill 55.5
Bill 64.4
However if you need to round things, what I might do is use a computed column for this. You can look up syntax, but something like:
Student Mark RoundedMark
--------- ------ --------------
Bill 88.5 89
Bill 55.5 56
Bill 64.4 64
October 4, 2015 at 7:24 am
I agree, the only viable option is to normalise, and why store rounded values; you have the value, use whatever report generator you use to round or format the output.
...
October 29, 2015 at 8:57 pm
happygeek (10/4/2015)
why store rounded values; you have the value, use whatever report generator you use to round or format the output.
It depends.
You may need to consider those rounded values in a follow-up report, which should use the values as they've been reported, not as they are.
_____________
Code for TallyGenerator
October 29, 2015 at 10:25 pm
Sergiy (10/29/2015)
happygeek (10/4/2015)
why store rounded values; you have the value, use whatever report generator you use to round or format the output.It depends.
You may need to consider those rounded values in a follow-up report, which should use the values as they've been reported, not as they are.
Good point. But all reports should have the same consistent approach surely? Would love to know of real life examples.
...
October 29, 2015 at 11:16 pm
Real life example?
Tax invoice.
Tax amount usually is calculated as percentage value, and usually needs to be rounded.
Those rounded values are what is paid and shown in reports.
When you aggregate - you need to summarise the values after rounding, not the ones calculated as Invoiced Value * Tax Rate.
_____________
Code for TallyGenerator
October 30, 2015 at 1:26 am
When storing the data in the database try and store it with as much detail as possible. for example if you have the data here is why
create table #ranks
( studentid int ,
[subject] varchar(20),
Score decimal(5,2),
Rounded as round(Score,0))
Go
insert into #ranks
select 1 , 'Math' , 85.67 union all
select 1 , 'English' , 77.64 union all
select 2 , 'Math' , 85.65 union all
select 2 , 'English' , 77.68 union all
select 3 , 'Math' , 99.49 union all
select 3 , 'English' , 77.50
GO
select Studentid , sum(Score) , Sum(Rounded)
from #ranks
group by Studentid
-- Notice how Student 1 and 2 share the same Score if rounded , now who gets first rank?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply