Hi all,
I've been thinking about the database schema for the logging project.
The first thing I wanted to do was the schema for the country database.
Here's the SQL I've come up with. I got a list of DXCC countries
off the newsgroups. There was one line per country, with a list of
prefixes, list of CQ zones, list of ITU zones, list of
continents, the timezone offset and latitude/longitude. (Some
DXCC entities even have multiple continents!)
Eventually that's the same scheme I used for this part of the DB.
The country table is keyed on the country name. The prefix table
is 1:N country:prefix, same for CQ zones, same for ITU zones,
Anyone know any good tools for drawing ER (entity-relationship) diagrams
and perhaps generating SQL from them?
I've also attached the data I used to populate the table (2K lines).
There's a field in there for ADIF zone numbers (see http://www.hosenose.com/
for ADIF details), which I haven't filled in yet.
All comments welcome. Next task is the main log schema. Should we start
a separate mailing list (at onelist?) for those interested in this
project?
Hamish
drop table country;
create table country (
name text PRIMARY KEY,
deleted bool,
timezone int2,
latitude float4,
longitude float4,
adifnum int2
);
drop table continent;
create table continent (
country text,
continent char(2)
);
drop table prefix;
create table prefix (
country text,
prefix varchar(3)
);
drop table cqzone;
create table cqzone (
country text,
zone int2
);
drop table ituzone;
create table ituzone (
country text,
zone int2
);
data.sql.gz
