Link to home
Start Free TrialLog in
Avatar of mugsey
mugseyFlag for United Kingdom of Great Britain and Northern Ireland

asked on

database schema for angling club

I need to design a database schema for an angling clubs that lists the history of the club.

I need to store details of

anglers  (personal details, address etc)
   who belong to a
club (club details, address etc)

now

each anglers international competition results (year, competition, location, result)

each anglers national competition results (year, competition, location, result)


What would be the database schema for this?
Avatar of dragos_craciun
dragos_craciun
Flag of Romania image

Well ... there are a lot of problems, for example may an angler belong to two or more clubs?
Depending on this a design may vary.
You may create tables for:
-Anglers(angler_id, name, address)
-Clubs(club_id, club name, address)
-AnglerClubs (if an angler may belong to more clubs: angler_id, club_id. If not, put club_id in Anglers table)
-Competitions(one table with flag for national/international, name, no location, no year)
-Locations
-CompetitionOccurence(competition id, location, date)
-Results(angler_id, competition_occurence_id, result)

Please be aware that if you want to have history, even if anglers belong to only one club they may move from a club to another, or leave the club. In this case you are forced to create the AnglerClubs table and add Start_Date, End_Date to it
Avatar of mugsey

ASKER

OK thanks very much - so if an angler can belong to more than one club through their career what would you do?   Also to keep it as simple as possible
ASKER CERTIFIED SOLUTION
Avatar of dragos_craciun
dragos_craciun
Flag of Romania image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial