Unsure how to design this new 'score board' database

[Sharky II]

Hi there

been given the chance to design a brand new db. It will be a score keeping system for an annual charity event with abotu 20-40 teams of 4 members. There will be 10 games/activities played by each team over the course of 2 days.

For each activity, the members of each team are given points. These points are totalled and this makes up the teams score for that activity. Every hour and half (the time it takes to complete each activity and get to the next one) the scoreboard will need to be updated, depending on how they've just done in that activity.

Do you guys think this would be better suited to a spreadsheet application? Any advice on how to structure things (and also what to view - the forms etc) would be appreciated

Many thanks

 

[KenHigg]

Q. Is this a freebie where you may get some referal work if someone sees it?

If No then solution = xls

ken

 

[Sharky II]

hi

no it's a real job. They want a 'real-time' scoreboard that will display the latest scores. It will probably sit on a laptop back at the youth hostel and be projected on a wall or screen at all times - so they would need the results to be displayed in some kind of neat way where it shows all of the team places.

The excel way (i've never used excel) would be a vertical row of teams, and a horizonal row of activities, and then a 'total' column.

However if i normalise things properly in access, this actually makes things more difficult.

Anyway, anyone have any ideas/advice? Anyone done this kind of thing before? Would love to hear from you

Cheers

Eddie

 

[ReAn]

Get a copy of photoshop and make a spiffy looking scoreboard that has an OnTimer event (1000 ms delay or something) that grabs the info from a split db (back end db with linked tables) and updates the scoreboard.

The reason for split is so you can enter in new data w/o disrupting the cool looking scoreboard you have projected. Which auto-updates itself from the db over a network, hell it can be as simple as 2 laptops connected 1to1 with a patch cable, but that way you can enter data and not have it pop up on the overhead.

Then to store your info try this table structure:

tblTeams
TEAMID
TEAMNAME

tblParticipants
PARTICIPANTID
PARTICIPANTNAME
TEAMID

tblGames
GAMEID
GAMENAME

tblScores
SCOREID
SCOREPOINTS
TEAMID
GAMEID

I hope you can see how the relations work and how to store the data.

 

[Sharky II]

Thanks for that rean.

I had originally thought of a structure liek given in the attached jpg.

ONE team has MANY members. You can't have a many to many relationships between teams and activities (or games, or whatever) so you need an activities details table to act as a mediator.

I never though of having a proper 'scores' table. Will look into that!

Thanks again!

 

[Sharky II]

I see that the 'scores' table is effectively the same as the ActivitiesDetails table in mine

 

[ReAn]

You like the split idea for presentation purposes?

 

[Sharky II]

Yeah sounds good!

I'm having problems thinking of a good way to structure the points adding system... can't think how thsi should be done, or where to keep the 'total'... how do you think i should add up all the points for each team, for all the events? hmmmm

perhaps make an expression in a query...? hmmm

 

[ReAn]

well with my method you just go

SQL = "SELECT Sum(tblScores.SCOREPOINTS) AS SumOfSCOREPOINTS FROM tblScores WHERE tblScores.TEAMID = " & TeamIDIWantToGetTheScoreFor

You can prolly adapt this for yours.

 

[KenHigg]

Cool... I'd like to see this when you get it built...

ken

 

[Craig_Withers]

I wouldn't mind seeing this myself actually, it sounds pretty interesting.

 

[Sharky II]

Hi

they've made it more complicated now - they want it so that when you enter the number of points a team makes for any event, it will automatically order the teams (1st place, 2nd place etc - based on whoever got the highest score or, for example who completed a task in the shortest amount of time - it needs to be able to work this out) and if you get 1st place you get 40 points, if you get 2nd you get 38, 3rd place is 36 points.... etc.

Which makes it a bit of a bitch. Getting paid pretty much nothing for it.

I can't think of how to make the interface for this work. Any suggestions lads?

Cheers

EDIT - The rank function/query thing is damn difficult

 

[Sharky II]

anyone?