PostgreSQL – Design Question for Almost Identical Tables

database-designpostgresql

I am doing a project for my badminton club.

Intro

I want to create a database for players registration, such as the following:

CREATE TABLE Registration(
    PlayerName_fkey text,
    SchoolName_fkey text,
    Category text,
    TournamentNumber integer,
    TypeOfPlay text,
    Selected boolean
);

Note: It is not displayed here, but each column besides "Selected" is basically a foreign key to another table.

Question

For the registration section, I have entries for single play and doubles play.

The registration table would be almost identical, except the doubles have two players instead of one. I have the following choices:

  1. Create two tables, one for single players, one for double players
  2. Create one table and add an extra column double_partner which is always null for singles players.

Both options seem bad to me and I was wondering if there was another better option out there.

Thanks in advance!

Best Answer

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
  Address_fields..
  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?
  Other_fields...
  ..
);

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 :-)
  ..
  Other_fields
  ..
);

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_start_date DATE,
  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_school_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_school_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).

A+

Related Question