Page 3 - Football League Table [design / layout / relations]

Correct just found that out after playing around with the script, great bit of code as it does everthing i been trying to do for a month

kenny

How would you go about comparing the previous position with the latest position for each club in the table?

That is (potentially) too big a question to be so quietly tacked on to the end of such an ancient thread.

Does it really matter if the thread is ancient? I'm just merely asking how it can be achieved. I'm sure
Guelphdad would have some idea, as I thought his SQL query was great.

yup, it does

i, for one, just don't feel like reading two entire pages of a dead thread to get up to speed on some database design that you likely aren't even using

what you should do is present a test case, in a new thread, that gives your table layout, sample rows, and the results you want from the query that you want help with

that's s.o.p. for asking a forum question

I'm actually using what Guelphdad posted on page 2 of this thread, as I'm just trying to add things to it. So, I should create a new thread with what Guelphdad's already posted?

I looked at the database design and I must admit that I find it incorrect in terms of how it does home and away teams.

I don't know european football but I have built a database for basketball that mimics this basic idea, and the table with the teams in the games doesn't work (ideally) in my opinion.

In theory it seems like you want to have all the home/away team in the same database table, but when yo uwant to do queries to find a teams record/performance based on whether or not they are home or away - it becomes complicated to write the queries.

I've found that when you create these tables you have a 'parent' games table that is pretty basic with specific information, game date, location if it matters, attendance, stuff specific to the GAME itself.

Then you have a 'teams in the game' table. which has a foreign key that references the games table, a field for the team name, a field for whether the team was home or away and a field for whether the team won or lost, this allows easier queries. (If you don't want to track scoring by halfs/overtime yo ucan also put the final score in this field)

I don't really understand the standings question you are asking, if you want a running standings, I've built a query for that too (using american baseball day by day records) and it's a query that aliases the same table three times.

I don't know european football but I do know 'league creation' databases, please let me know if you want some help with the normalization as the version on page 2 isn't properliy normalized in my opinion

yes, please if can help with the normalization that would be great. If you visit this url "bbc.co.uk/sport/football/premier-league/table", I think you'll understand what i meant. Thanks