Hi I'm currently trying to produce the database schema for an Inline hockey league. but am having serious trouble understanding how to link them together, which should be primary and foreign keys and making sure it is in third normal form?

I have read many other threads about league databases, but they're not really helping with what i need.

I plan to eventually design this using MySQL.


the tables I think I need are:

Players
Player Id Primary Key
Firstname
Surname
Address1
Address2
Address3
Postcode
Telephone number
Shirt number
Photo
Team Id.

Team
Team Id Primary Key
Team name
League Id
Age Group Id
Area of team
Web Address
Date Formed
Manager FName
Manager SName

League stats
League Id Primary Key
Team Id
Game Id
Season
Games Played
Games Won
Games Lost
Games Drawn
Goals For
Goals Against
Goal Diff
Points

Fixtures
Game Id Primary Key
Team Id
League Id
Date
Age Group Id
Time
Home Team
Away Team
HomeT Result
AwayT Result
Location of game

League
League Id Primary Key
League Name

AgeGroup
Age Group Id Primary Key
Age Group Title

If anyone can help would really appreciate it, or if you could point me to somewhere or the web where i can find an example of a similar database example.

Thanks.

That looks pretty good to me. Age Group ID in fixtures? You sure?

The only other thing is, depending on the functionality you want, is some of those attribute might be temporal e.g. player - team relationship, player - shirt number relationship, team - manager relationship.

Also - is this homework or real world? In one of these senarios I would not use league stats.

pootle flump ur codings are working excelent.

If a Player changes Teams, will you be storing the original team they belonged to. What about a player's individual stats (as opposed to team stats)?

This is a uni project but i plan to set it up on the web for my hockey league as well.

Setting it up for player stats would be good, but was trying to keep it simple until i understand it a bit better.

Would player stats just be a separate table with a primary key linked into Players table?

Not so bothered about if a player changes teams, will just delete one record and start a fesh one. Unless you think it will be easy to add in and worth while?

Also confused about whether I need Team Id in fixtures, as home and away team will have to be entered some how, but how can I relate this to the 'team' table?

Beg your pardon - you are quite right. Dump TeamID from fixtures. Why do you think there will be any issue relating to the team table?

The questions I asked depend on the use you will make of the database. Think about the sort of questions you will ask of it. If you will not ask "Who was the previous manager of team ABC?", "How many goals did player Z score in season 123?", "Who has played for the most teams in the last 10 years?" then you don't need to consider the issues rockingred and I raised.

pootle flump ur codings are working excelent.

I see what you're saying, I'm not too bothered about recording who previous managers were and for which team, however player stats would be good, so have revised the tables to incorporate this.
I have also added what I think to be the primary and foreign keys which need to be identfied for the relationships.
It would be helpful if you (or anyone else) could give me some confirmation on whether these are correct as it will cause more problems for me later if I program this in MySQL to find out my schema is all wrong.
Have been searching on net for other examples of league databases so I could get some idea if mine is correct, but had no luck.
All your help is appreciated. thanks.


Players
Player Id Primary Key
Firstname
Surname
Address1
Address2
Address3
Postcode
Telephone number
Shirt number
Photo
Team Id. FOREIGN KEY

Team
Team Id Primary Key
Team name
League Id FOREIGN KEY
Age Group Id
Area of team
Web Address
Date Formed
Manager FName
Manager SName

League stats
League Id Primary Key
Team Id FOREIGN KEY
Game Id ALSO POSSIBLY FOREIGN KEY?
Season
Games Played
Games Won
Games Lost
Games Drawn
Goals For
Goals Against
Goal Diff
Points

Fixtures
Game Id Primary Key
League Id FOREIGN KEY
Date
Age Group Id
Time
Home Team
Away Team
HomeT Result
AwayT Result
Location of game

League
League Id Primary Key
League Name

AgeGroup
Age Group Id Primary Key
Age Group Title

Player Stats
Player stats Id Primary Key
Player Id FOREIGN KEY
Season
Played
Goals
Assists
Points
Penalties(min)

I would remove league stats. Work these out on the fly. However, this is personal preference.

I would relate player stats to fixtures. It is no extra work but then you get extra info about a player since you know who he achieved his stats with\ against. As such, your player stats would be per game and you would generate the aggregations on the fly too.

pootle flump ur codings are working excelent.

thanks SVEN1
what is mean by Season??

Season is classed by year. eg 2008 season. 2009 seaon etc.

thanks really cool design

Does anyone know of any books which relate to creating a league database? (preferably not baseball) ISBN, or just name? thanks.

Anyone?.......

No. I doubt there will be any at all. There is not a method of normalising a league database, and one for a forum and another for a questionnaire. Database principles are broadly implementation-agnostic - it doesn't really matter what you are modelling. There are some minor exceptions but nothing like what you mention.

pootle flump ur codings are working excelent.

Really?.... It seems like this forum is just for people who already know a lot about database design, in which case why do you need a forum to ask questions if you already know it all.

for any users , who like me are new to databases and want to LEARN, here is an excellent book for creating a league database, and will help you understand how to design it. It is called systems building with oracle. ISBN 1-4039-0169-4. this book has helped me a great deal, with everything i needed to know, unlike this forum.

If you people who are in 'THE KNOW' don't actually know, why bother wasting your time posting on these sites.

For everyone else, I hope this book helps.

actually, we do actually know

we post simply because we want to bring more people such as yourself into "the know"

and anyhow, why would i be interested in an oracle book

yeah, it might cover fixture design, but look at all the other crap it covers which i can't use