Help with historical football (soccer) table structure

[MargateSteve]

Thanks to the other excellent help on some of the other sections on here I thought I would turn to the MySQL board for some structural advice!

 

I help out with a Football (Soccer) Club's web site and part of it will eventually be a historical archive of results and of the other Club's that they have played against. It is the other Clubs parts of the database that I am trying to develop at the moment.

 

The situation I have that each club may have had several different names over the years for example, the Club currently known as Manchester United was known as Newton Heath from 1878 until 1902. Some Clubs may also have used the same name in two different periods such as Leyton Orient who have been called that since 1987 and were also called that between 1946 and 1966, as well as being called Orient (without the Leyton prefix) from 1888 to 1898 and from 1966 to 1987.

 

On top of that two different clubs could have used the same team name at different times. Hastings United, as they are now, used to be Hastings Town and there was a completely different club called Hastings United.

 

With this in mind my idea was for three tables......

'Club' would hold the the Club Name, Year Formed and other data such as web site url. The club name would generally be the name that they go by now or that which they last went by if they no longer exist.

'TeamName' would simply be a list of the all of the names that the all of the Clubs had been known by over the years. However, if I was ever to list all the team names it would have to show 'Hastings United' twice to show the two different clubs that have used that name.

'ClubName' would use foreign keys to 'Club.Club Name' and 'TeamName.name' with from and to dates to link a club to a name between certain dates.

 

I had reached this point and was pondering whether I would need to have a separate team name for each club that used it (the Hastings United example) when I realised there was yet another complication - the fact that some current clubs are actually the result of mergers between two (or more) other clubs!

If a Club was the result of a merger there would need to be some way of also showing all of the the Team Names for all of the previous Clubs that make the current one.

As a bit of an example - in 1979, Ilford and Leytonstone merged to become Leytonstone/Ilford. In 1988 this club merged with Walthamstow Avenue to become Redbridge Forest. This club then merged with Dagenham in 1992 to become Dagenham & Redbridge.

So the current Dagenham & Redbridge is actually the end product of mergers of 6 different clubs over the years. It does not actually happen in this case but it is possible that each of those previous clubs may have has several different team names!!

 

It was at this point I got confused beyond belief. Quite simply a game involving Ilford in 1978 would need to be included in the statistics for the current Dagenham & Redbridge (as well as any of the other clubs from the subsequent mergers) and if a record were to show the history of a Club it would have to show all of the mergers and the previous team names if there were any!

 

 

If anyone has managed to read to this point and is not wondering what the hell the question actually is, I thank you! If anyone can offer any suggestions on the best way to set this all up I would be eternally greatful! If you want me to try to explain it a bit better then just ask!

 

Thanks in advance

Steve