SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Football (soccer) scoring system
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

markmce
Starting Member

United Kingdom
7 Posts

Posted - 07/21/2014 :  07:22:22  Show Profile  Reply with Quote
Hi, I have been given the task of creating a football scoring system using sql server database. I then have to implement this into asp.net to create the web design.

The database is given me a real headache and i dont know what tables to create and what relationships should be put in place.

So the instructions I have been given are as follows

The league will have a maximum of 12 teams

Teams must play at least 6 games each

Each team will play at least 3 games as “Home” team and at least 3 games as “Away” team.

The league will be split in 3 “Groups” of 4 teams each.

Teams are only expected to play against other teams in the same group. Team allocation to groups should be random at the start of each championship.

The system will then produce a “Team Standing Table” for each “Group” showing the team standing, accumulated goals scored, accumulated goals against, won drawn and lost games, and points accumulated. Points are awarded as follow: 3 points per game won, 1 point per game drawn, 0 points per game lost.

ahmeds08
Aged Yak Warrior

India
656 Posts

Posted - 07/21/2014 :  08:28:38  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
To start with create two tables one for Teams and the other for Groups.
you can add groupid(foreign key) in teams table which will be a bridge between teams and groups tables,so that you will know whch team belongs to which group.

you will get more advice here.

Javeed Ahmed
Go to Top of Page

markmce
Starting Member

United Kingdom
7 Posts

Posted - 07/21/2014 :  10:54:54  Show Profile  Reply with Quote
I will get more advice where? You didn't seem to include the link

mark mcevoy
Go to Top of Page

markmce
Starting Member

United Kingdom
7 Posts

Posted - 07/22/2014 :  14:52:07  Show Profile  Reply with Quote
Could really use some more help on this please...
Go to Top of Page

markmce
Starting Member

United Kingdom
7 Posts

Posted - 07/28/2014 :  08:30:22  Show Profile  Reply with Quote
Bump


mark mcevoy
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 07/28/2014 :  17:24:52  Show Profile  Reply with Quote
Let's start from the beginning... What are the entities that you are trying to model? What are the attributes of each entity? How are your entities related (if at all)? From your description you have:
1) Team - Name, Group (Foreign key to Group)
2) Group - Name
3) Games - Home Team, Away Team (Each have a foreign key to Team), Home score, Away score (Scores could be Null to indicate that the game has not been played yet)
You don't mention it but Game probably has a Date/Time and location defined and perhaps a length which might vary for youth games

How the teams get placed into Groups and how the games are scheduled should best be done at the application layer; it could be done at the SQL level but not every problem looks like a nail.

Now, what do you want to output? A team's points would be:
select
  t.Group,
  t.Name,
  sum(case
      when t.Name = g.HomeTeam then
         case
            when g.HomeScore > g.AwayScore then 3
            when g.HomeScore = g.AwayScore then 1
            else 0
            end
      else
         case
            when g.HomeScore < g.AwayScore then 3
            when g.HomeScore = g.AwayScore then 1
            else 0
            end
      end
      )
from
   games g
inner join teams t 
   on t.Name in (g.HomeTeam, g.AwayTeam)
group by
   t.group,
   t.name
Does that get you started?



Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 07/29/2014 :  18:25:50  Show Profile  Reply with Quote
Could really use some more help feedback on this please...



Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

406 Posts

Posted - 07/29/2014 :  20:05:58  Show Profile  Reply with Quote
Should the system take into account, that a game can be won without being played (one team doesn't show up)? Or a team looses even though they scored more goals than opponent, due to leaving the game in protest or 5 red cards?
How about first/second round and semifinals?
Should system track players in suspension?

Just some thoughts...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000