From eb9ee3742ba220deebc83771b03ffdc7f6a4d59c Mon Sep 17 00:00:00 2001 From: "Steinar H. Gunderson" Date: Wed, 30 Mar 2005 13:37:08 +0000 Subject: [PATCH] Begin changing the country stuff. - All players must now have a country. - There are clubs. - Countries have three-letter IOC country codes. The changes are not yet reflected in the data. --- sql/basic-data.sql | 3 +-- sql/ccbs.sql | 15 ++++++++++++++- 2 files changed, 15 insertions(+), 3 deletions(-) diff --git a/sql/basic-data.sql b/sql/basic-data.sql index 614d1f6..c66825c 100644 --- a/sql/basic-data.sql +++ b/sql/basic-data.sql @@ -2,8 +2,7 @@ INSERT INTO machines (machinename) VALUES ('Dancing Stage EuroMix 2'); INSERT INTO machines (machinename) VALUES ('DDR Extreme'); INSERT INTO scoringsystems (scoringsystemname) VALUES ('10K Machine score'); -INSERT INTO countries (countryname) VALUES ('Europe'); -INSERT INTO countries (countryname) VALUES ('Norway'); +INSERT INTO countries (countryname) VALUES ('Europe', 'EUR'); -- special exception INSERT INTO seasons (seasonname) VALUES ('European Cup'); INSERT INTO seasons (seasonname) VALUES ('Challenge Cup 2003'); INSERT INTO seasons (seasonname) VALUES ('Challenge Cup 2004'); diff --git a/sql/ccbs.sql b/sql/ccbs.sql index 839dd86..2b1a686 100644 --- a/sql/ccbs.sql +++ b/sql/ccbs.sql @@ -10,8 +10,19 @@ CREATE TABLE machines ( CREATE TABLE countries ( country SERIAL PRIMARY KEY, countryname VARCHAR NOT NULL, + countrycode CHAR(3) NOT NULL, -- IOC country code - UNIQUE ( countryname ) + UNIQUE ( countryname ), + UNIQUE ( countrycode ) +); + +CREATE TABLE clubs ( + club SERIAL PRIMARY KEY, + clubname VARCHAR NOT NULL, + clubcode CHAR(3) NOT NULL, + + UNIQUE ( clubname ), + UNIQUE ( clubcode ) ); CREATE TABLE songs ( @@ -60,6 +71,8 @@ CREATE INDEX songratings_feetrating ON songratings ( feetrating ); CREATE TABLE players ( player SERIAL PRIMARY KEY, nick VARCHAR NOT NULL, + country INTEGER NOT NULL REFERENCES countries, + club INTEGER REFERENCES clubs, UNIQUE ( nick ) ); -- 2.39.2