Database Normalisation (maybe)

[stb74]

I have a soccer/football website and have a DB table for divisions

 

ID_DIVISION

ID_LEAGUE

division_name

abbrev

divisions_line

 

1  1        Premier Division  PremierDivision  1,12

 

The names and division lines never changed up until last year so I added another DB table called division_details

 

ID_LINK

ID_DIVISION

ID_SEASON

ID_LEAGUE

division_line

 

The divisions DB table hasa structure of

 

ID_DIVISION

division_name

abbrev

 

This enables me to use the division_details DB table to have different data each season as with new seasons there may be more divisions or the division_lines may be different each season.

 

What has happened this year is that One of the leagues has changed its division structure which means that if I change the name of the division under the divisions DB table, it will change the name for all seasons.

 

What I am looking help for is the best way to have the DB tables, I can move the division_name and abbrev into the division_details DB but then that will leave one field in divisions.

 

Apologies about the explaination.

[Zwiter]

Try to find tutorial about the way to make a normalisation with the 3rd normal form.

If you dont find anything, i can provide you some help, though it is quite long to explain, and it is not so difficult to understand.

With small table, usualy, you use the 3rd normal form without paying attention to this

Good luck.

Z.

 

PS : i did not understand the way your database should work

[stb74]

Zwiter

 

Do you follow football

[Zwiter]

thought i am french ( france ), not at all

I only know about baseball in France, and about normalisation algorithm

Sorry that i could not understand the structure you need and provide a better help.

Z.

[stb74]

Not to worry hopefully I get this right my version in brackets,

 

French Championships(league)

Elite (Division)

Nationale A (Division)

Nationale B (Division)

 

Each Season the names do not change, this was like my system there had been no changes, then the last two seasons the league has decided to restructure and rename the Elite Nationale A and remove Nationale B.

 

The problem I have is that if I change the name it means that for each season the name will be the new name, what I would like to have is last season it stays Elite and Nationale A and this season I can have the new name.

 

 

[Zwiter]

the question is: what about the future.

I mean, do you plane to change it again if they will change the league stuff again?

 

And how do you plane to change it?

static stuff: alterate the databse each time

dynamic: there is a table with diffrents names according to the year for exemple.

 

And last, what are you trying to do with that ?

 

But i think the best way is to clearly write all the relationship between each elements, and try to normalise that.

For example:

ID_LEAGUE -> ID_DIVISION  (to each ID_LEAGUE corresponds only one ID_DIVISION)

division_name -> ID_DIVISION (to each division_name correspond only one ID_DIVISION)

...

 

You can use many ellement to define another one :

ID_LEAGUE, division_name -> ID_DISION (to each couple ID_LEAGUE, division_name corredponds only one ID_DIVISION)

 

Z.

[bluejay002]

i suppose (am not sure if am getting the idea though ) what you want is to maintain history.

 

from:

ID_DIVISION

division_name

abbrev

 

to:

ID_DIVISION

division_name

abbrev

void

 

i added a void, there, you can specify if that certain division is active or not, but still maintains the history. am not familiar with football so its kinda hard for me to give an insight of how the structure should look like.

 

but usually, maintaining a relationship requires a relationship table, a table that have all the necessary fields from different tables to maintain their relationship without too much mods even if you had changes later with the league (logically).

[stb74]

I had to move onto other things, but now I am back to get this working.

 

bluejay002

 

I do have a table to maintain history and use seasonID to link them together

 

Table 1

ID_DIVISION

division_name

abbrev

 

Link Table

ID_LINK

ID_DIVISION

ID_SEASON

ID_LEAGUE

division_line

 

I created the link table as each season the division_line can change, this new table allowed me to cater for that.  But as I said before now with some restructuring the division_name can change.

 

What I have tried to do is create another table,

 

name Link Table

ID_LINK

ID_DIVISION

ID_SEASON

division_name

 

I have one entry in here and it contains the new name for the division I wanted changed. I could probably combine the two link tables.

 

With the following query

 

SELECT 
d.division_name, dc.division_name
FROM divisions d
LEFT JOIN division_changes dc ON ( d.ID_DIVISION = dc.division_id AND dc.season_id = '4' )
JOIN division_details dt ON ( d.ID_division = dt.ID_DIVISION )
JOIN leagues L ON ( d.ID_LEAGUE = L.ID_LEAGUE )
WHERE DT.ID_LEAGUE = '9' AND DT.ID_SEASON = '4'

 

Returns

 

d.division_name        dc.division_name

Premier League  IFA Premiership

First Division            IFA Championship

Reserve League       NULL

IFA Youth Section A  NULL

IFA Youth Section B  NULL

 

 

Which is what I am looking for, but my question now is, can I return d.division_name for the ones that don't have a dc.division_name and vice versa.  Or do I have to use php to display the appropriate names

 

So I am returning

 

division_name

IFA Premiership

IFA Championship

Reserve League

IFA Youth Section A

IFA Youth Section B

 

[fenway]

You can use IFNULL( dc.division_name, d.division_name ).