• Some users have recently had their accounts hijacked. It seems that the now defunct EVGA forums might have compromised your password there and seems many are using the same PW here. We would suggest you UPDATE YOUR PASSWORD and TURN ON 2FA for your account here to further secure it. None of the compromised accounts had 2FA turned on.
    Once you have enabled 2FA, your account will be updated soon to show a badge, letting other members know that you use 2FA to protect your account. This should be beneficial for everyone that uses FSFT.

MySQL 5 query question

N

nismo_r34

Limp Gawd
Joined
Feb 16, 2003
Messages
491
I'm putting together a small website for a mini-golf tournament my friends and I have going, but I'm having trouble writing a query to count the number of "hole in one" scores for each particular player.

I've tried using the full text search but am unable to get the query to return anything.

My results table columns are:

id, player_id, location_id, schedule_id, specialevent, round, [hole1score,...hole18score], totalscore, frontnine, backnine

I suspect that using a select count would lead to innacurate numbers since a given row (which would represent one round of mini-golf by a particular player) may have more than one hole with a "hole in one".

Any help would be appreciated, its late, i'm sick, and i'm stumped.

Thanks in advance.

 
Try something along the lines of

Sum((Case holescore1 when 1 then 1 else 0 end)+(Case holescore2 when 1 then 1 else 0 end)+(Case holescore3 when 1 then 1 else 0 end)+...)

Someone correct me if I'm wrong, or if there is a much better idea...

You could always do a count for each column and sum that... ugh I need sleep, but the first one might work for you.
 
What you ought to do is normalize your database. Having 18 columns, one for each hole is not a very good design. Make a new table looking something like:
ID (same as the ID in your existing table, provided it's the primary key)
Hole
Score
 
I don't see what's denormal about the representation you're using, but making a relationship table as Nevermind suggests will help you write a query since the scores can be examined in a set-wise manner.

MySQL still doesn't support PIVOT, which is a language feature that would make writing your query trivial against the database structure that you already have.
 
It violates 1NF, as I understand it, because not all golf rounds consist of 18 holes. Please correct me if I'm wrong.
 
Oh, I see. It'll come down to your definition of the entity. We don't have one, so I don't think we can assume games are 18 holes (always) or not. Which means we don't know if the entity is decomposible or not.
 
our golf games are always 18 holes. typically if a course has 19 holes you substitute your score on hole 19 for your highest score on the previous 18, which keeps the number of holes i need to keep scores for to 18.

thanks for the suggestions guys.

 
I'm curious, is it considered bad design to include the totalscore, frontnine, backnine columns in a table because of redundancy issues? Does including them violate any of the normal forms?

I'm not trying to attack the design, however. I just want to know for future use.
 
it wasn't redudant to me because i didn't want to have to write a query to calculate those values, i just had the page do it when inserting the data into the table for easy access later.

 
Jason Isom said:
I'm curious, is it considered bad design to include the totalscore, frontnine, backnine columns in a table because of redundancy issues? Does including them violate any of the normal forms?
Click to expand...
That's one of those simple questions with a really long answer.
 
mikeblas said:
That's one of those simple questions with a really long answer.
Click to expand...


I don't know...it's not that hard. General rule of thumb:
Don't store calculated fields in a database. It's waaaay to easy for someone to update one of the values the total consists of without also updating that total field. That leads to incorrect data.
 
deuce868 said:
I don't know...it's not that hard.
Click to expand...
If you ignore the subtlties, sure.

deuce868 said:
General rule of thumb:
Don't store calculated fields in a database. It's waaaay to easy for someone to update one of the values the total consists of without also updating that total field. That leads to incorrect data.
Click to expand...
Are you referring to the logical model? If so, I'd agree, given the definitions that we've been using in the thread. But I don't think you're referring to the logical model, since most people don't assume it can do any computation at all.

In the physical model, you'll probably find that your database server has three or four ways to protect fields, or calculate fields automatically when others change, or even not calculate them at all and compute them only when they're queried. So it's only way too easy to cause a data integrity problem if there's some bad administration going on, or we're using a less advanced product.

Examining data warehousing, we'll notice that storing calculated values in a database is what the practice is all about! For example, amazon.com doesn't scan and aggregate every time a viewer looks at a page to see what similar books other people liked; that information calculated and stored, and refreshed every once in a while.
 
mikeblas said:
If you ignore the subtlties, sure.

Are you referring to the logical model?
Click to expand...
I'm referring to the original poster. I mean...the guy is storing golf scores. I think having his app to the math on results for him would be a better way to go to prevent inaccurate total scores.


mikeblas said:
Examining data warehousing, we'll notice that storing calculated values in a database is what the practice is all about! For example, amazon.com doesn't scan and aggregate every time a viewer looks at a page to see what similar books other people liked; that information calculated and stored, and refreshed every once in a while.
Click to expand...

I would just consider that a caching mechanism for those summations. They store these caches in a db. You could do it to a file or ramdisk. I doubt that those calculations are performed on each update to the system. They're still calculated and then cached for quicker access.

I guess I've just put together my share of web apps that need to do some simple averages, totals, etc. I've found it best to let the app do the math for me. Now are any of my apps Amazon? Of course not. My advice to the OP...don't store the totals. Store the single values and write a function to calculate your totals for you given xx db records.
 
i think we're getting a little carried away for a tournament that will net the winner a free pitcher of beer :p

 
nismo_r34 said:
i think we're getting a little carried away for a tournament that will net the winner a free pitcher of beer :p

Click to expand...
WAIT! There's beer on the line here? OMG. Ok, first we need an oracle cluster license with two independant J2EE application servers each doing the math and validating against each other. Once the matches have been calculated we can then....

oh wait...only one pitcher? Is it at least good stuff?
 
winner's choice. i forgot that the other part of the prize is a plate of chicken nachos... oh and bragging rights, which is by far the best prize ever.

 
nismo_r34 said:
winner's choice. i forgot that the other part of the prize is a plate of chicken nachos...
Click to expand...
Seriously, if it's that important, you can't understimate the design.

deuce868 said:
I'm referring to the original poster.
Click to expand...
Oh, I see. I had thought you were responding to Jason Isom, who had asked a more theoretical question.
 
You must log in or register to reply here.
Back
Top