0

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!

  • 1
    I think you should have one table to register all individual players, and a second one to register the couples. One player can be a part of more than one double team. – McNets Nov 21 '16 at 22:19
  • In my configuration, each player can only be part of one double team for each tournament. Does that have an impact on the answer? – Benoit Goderre Nov 21 '16 at 22:22
  • 1
    Unrelated, but: having the player's name be the FK (and thus a PK) seems dangerous (think about entering the name incorrectly, you need to change all dependent rows). If you are 100% sure that you will never, under no circumstance have more then two players, then adding a second player name does not sound too bad. But what about the other attributes? Is the school an attribute of the player or the team? Same for the category. – a_horse_with_no_name Nov 21 '16 at 22:47
  • You are right that using the name for FK and PK is a dangerous idea (what if two players share the same name?). I am planning to start using the player's id as the FK (my client needs to send me the list of IDs for each player). School and category would be an attribute for both the player and the team (if I understand your question correctly). – Benoit Goderre Nov 21 '16 at 22:51
2

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+

  • I was previously wondering about the creation of a Team table as you described, I am glad to see it in your design! Very thorough and detailed answer, thank you for all the effort. I will definitely use your design and read more on the link you mentined. Note: I do use PostgreSQL :). – Benoit Goderre Nov 22 '16 at 14:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.