wondering whether an expert out there could advise me on a db design

[tezza05]

Hi Guys

My knowledge is quite basic when it comes to database design and was wondering whether an expert out there could advise me.

I'm developing a site for a rugby club using PHP & MySql.

I'm currently working on a fixture section that lists all the up and coming events as well as previous matches over the past years.

The idea is too present fixture info for each of the 3 teams I.E 1st Team: date, time, opposition etc, that also links to a match reports, match results, team selection, opposition details and so on.

I just need some advice on whether Im going the right way about it.

This is the schema at the mo

TEAM TABLE (id, team)

OPPOSITION TABLE (id, team)

FIXTURE DATE TABLE (id, date)

FIXTURE DETAILS TABLES (id, time, ref, touch_judge, ground....)

TEAMSHEET TABLE (id, rightwing, scrumhalf....)

RESULTS TABLE (id, result, score, points, status....)

MATCH_REPORT_TABLE(id, report)

SEASON TABLE (id, year)

FIXTURE_LOOKUP_TABLE (contains id's from tables above)

I'm not too sure whether I've used to many multiple table and have too many fields in the lookup table

Any advice would be much appreciated

Thanks in advance

[Dr John]

I think you have more tables than you need. It's overnormalised.

One team table, not team and opposition - the id distinguishes between the sides, so why put them in separate tables?

Certainly NOT fixture date and fixture details as seperate tables - date and details are all attributes of the one fixture. Just have a fixture table.

I don't think you need result in the results table - this is a calculated value from the score (although it might make life easier for you to include it)

I think your season table is also redundant as year is an attribute of fixture (and is part of the date anyway)

This would automatically reduce the number of attributes in your look-up table a fair bit. It would also simplify your queries as less (unnecessary) joins would be required.

[Dr John]

Isn't it normal to have a player table as well? Where details of individuals are stored (but be careful about the data protection act and avoid personal data).

Teamsheet might give you problems as the teams would change with time. I think it should contain fixture_id, player_id and position.

However this has to be a commonly solved problem and you might be lucky and find someone who has one it already for some other team sport.

[tezza05]

Hi Dr John

Thank you very much for your reply, it makes perfect sense, I had a feeling I was getting carried away with the amount of tables.

Yes there's already a players table which only displays profile details to the public I.E position, fav player, comments etc

[tezza05]

Hi Dr John

Just a quick question is it necessary to have the results id in the lookup table or would it just make sense to have a results table with a fid number?

Thanks in advance

Tez

[Dr John]

The more I look at it the more I think that most of your tables are actually overnormalised offshoots of fixture! Apart from player, teamsheet and team, all the other things are just attributes of the fixture and should be in a single table.

I think you are actually making tables that correspond to queries you are likely to require - eg a table for match report - unless you have multiple reports on the same match/fixture, the report is an attribute of a single fixture an dhas no functional dependancy on any other attribute in fixture. Similarly results - a match/fixture has only one result, so it is an attribute of fixture, and so on. The id in each of these tables will all be the fixture id as the primary key. But you might run a query to display just the result or just the match report.

your way
fixture , id=7, your original attributes
result id = 7 - the result of fixture 7
report id = 7 - report on fixture 7


Fixture lookup then brings the ids into one table and necessitates lots of joins to get at the overnormalised data. So your look-up table would be 7,7,7 etc for fixture 7's data. Guess what the look-up will be for fixture with id=8 (there is no need to use any id other than that of the fixure itself to identify the report, result etc)

I now see the structure as follows:-

team--<fixture --- teamsheet>--player
one--many--one---one--many--one

fixture will have home_team and away_team, and both will use an id from team (can't draw the second relation into this text box). Fixture will contain all the attributes that you've split off into the other tables. No look-up required.

But as I said, if someone else has done this for some other team sport, they may have a different ERD to the one I'm suggesting, which has been analysed in a different way.

[crabby04]

yep that makes sense thank you once again for your help