Below is my first pass at a relatively simple system.
Don't know what RDBMS you're using, so your SQL
may vary a bit depending.
CREATE TABLE School
School_id INTEGER PRIMARY KEY,
School_name TEXT NOT NULL,
Address_1 TEXT NOT NULL, -- must have at least 1 address field
Tel TEXT NOT NULL -- I'm assuming that each school has a phone number,
-- put a UNIQUE CONSTRAINT on this. Different schools
-- can't have the same number! **But**, there might
-- be a boys and a girls school with the same office?
CREATE TABLE Player
Player_id INTEGER PRIMARY KEY,
Player_name TEXT NOT NULL,
Player_gender BOOLEAN, -- You forgot this one!
Player_DOB DATE, -- and this :-)
CREATE TABLE Team
Team_id INTEGER PRIMARY KEY,
Team_name TEXT -- put in UNIQUE? Don't allow any teams with same name even
-- from different schools.
Team_Player_id_1 INTEGER NOT NULL, -- Put UNIQUE CONSTRAINT on both player 1
Team_Player_id_2 INTEGER NOT NULL, -- and player 2.
CREATE TABLE Tournament
Tournament_id PRIMARY KEY,
Tournament_name NOT NULL, -- UNIQUE?
Tournament_type CHAR(1) CHECK Tournament_type IN ('S', 'D'),
Tournament_max_entrants INTEGER -- is there a max no of entrants?
For the first pass, I've decided to split the registration
tables into singles and doubles - there may be better/more
sophisticated methods of dealing with this.
You could think of having an Entrant table with either a
singles or a doubles entity and maybe (probably as part
of code and not in the RDBMS) ensure that teams (i.e.,
an Entrant with two members) can only enter doubles tournaments?
CREATE TABLE Singles_Registration
Reg_id INTEGER PRIMARY KEY,
Reg_entrant_id INTEGER FOREIGN KEY (Player) (Player_id),
Reg_Category text, -- don't know what this is for in your schema?
Reg_tournament_id FOREIGN KEY (Tournament) (Tournament__id),
Reg_selected Boolean -- foreign key to table X
CREATE TABLE Doubles_Registration
Dreg_registration_id INTEGER PRIMARY KEY,
Dreg_entrant_id INTEGER FOREIGN KEY (Team) (Team_id),
Dreg_category text, -- don't know what this is for in your schema?
Dreg_tournament_id FOREIGN KEY (Team) (Team__id),
Dreg_selected Boolean -- FK to table X
You might get more ideas from http://www.databaseanswers.org, here.
In particular, here and esp. here.
I think you might well need a bit more sophistication - you have
nothing about attainment levels/qualification_prerequisites,
heats or possibly even a tournament_location table - not every
school may be able to host a tournament and may use other
local courts. The list of potential fields is quite large. To
say nothing of results?
You don't mention your RDBMS, if you still have a choice and you're
going for an Open Source solution , I recommend PostgreSQL - it is
infinitely more capable than MySQL.
Neither do you mention your development environment - not that
it's critical, but it might be useful to know. The more information
you provide here, the better.
A Google of "sports tournament schemas" brought up several possibly
interesting sites (here inter alia).