True, zones are entities. What is DKNF?
Countries and zones are related, but I don't see the value of a separate table which
might look like this:
Zone - text
Country_ID(FK to countries table - unique)
14 SP
14 EA
14 F
as opposed to the existing countries table
Country_ID - text PK
Name - text
Zone - text
SP Poland 14
EA Spain 14
F France 14
True. But the number of countries contacted will not (except by coincidence) be the
same as the number of zones.
Sorry, I meant Zone. Station_ID can be callsign. Every amateur operator has a unique
callsign, the assignment of which is an arcane and fairly non-logical ritual.
OK.
I'm getting there, Thanks for your attention to this.
As Stefan asked, wahat is a member, what is a non-member?
Member of what? The designated group?
You can create a new database. Then open query design view and without selecting any tables, select
SQL. Now copy and paste the first CREATE TABLE statement into the window and on the top menubar
select Query and Run. The table is created. In turn do the same for each of the other CREATE TABLE
statements. Just past over what was there before.
When done, open the relationships window and show all the tables. Drag them around so it all makes
sense.
Just take this as an idea. I think it is about right, but maybe not.
CREATE TABLE Countries (
CountryCode CHAR(2) NOT NULL,
CountryName VARCHAR(50) NOT NULL,
CONSTRAINT pk_Countries PRIMARY KEY (CountryCode)
);
CREATE TABLE Zones (
ZoneNumber SMALLINT NOT NULL,
ZoneName VARCHAR(50) NOT NULL,
CONSTRAINT pk_Zones PRIMARY KEY (ZoneNumber)
);
CREATE TABLE CountryZones (
CountryCode CHAR(2) NOT NULL,
CONSTRAINT fk_Countries_CountryZones
FOREIGN KEY (CountryCode) REFERENCES
Countries (CountryCode),
ZoneNumber SMALLINT NOT NULL,
CONSTRAINT fk_Zones_CountryZones
FOREIGN KEY (ZoneNumber) REFERENCES
Zones (ZoneNumber),
CONSTRAINT pk_CountryZones
PRIMARY KEY (CountryCode, ZoneNumber)
);
CREATE TABLE Stations (
CallSign VARCHAR(6) NOT NULL,
OperatorName VARCHAR(50),
CountryCode CHAR(2),
ZoneNumber SMALLINT NOT NULL,
CONSTRAINT fk_CountryZones_Stations
FOREIGN KEY (CountryCode, ZoneNumber) REFERENCES
CountryZones (CountryCode, ZoneNumber),
CONSTRAINT pk_Stations PRIMARY KEY (CallSign)
);
CREATE TABLE DesignatedGroups (
GroupID AUTOINCREMENT NOT NULL,
GroupName VARCHAR(50) NOT NULL,
CONSTRAINT pk_DesignatedGroups PRIMARY KEY (GroupID)
);
CREATE TABLE DesignedGroupMembers (
GroupID LONG NOT NULL,
CONSTRAINT fk_DesignatedGroups_DesignedGroupMembers
FOREIGN KEY (GroupID) REFERENCES
DesignatedGroups (GroupID),
CallSign VARCHAR(6) NOT NULL,
CONSTRAINT StationsDesignedGroupMembers
FOREIGN KEY (CallSign) REFERENCES
Stations (CallSign),
JoinedDate DATETIME NOT NULL,
CONSTRAINT pk_DesignedGroupMembers
PRIMARY KEY (GroupID, CallSign)
);
CREATE TABLE GroupContacts (
GroupID LONG NOT NULL,
CallSign_One VARCHAR(6) NOT NULL,
CONSTRAINT fk_DesignedGroupMembers_GroupContacts
FOREIGN KEY (GroupID, CallSign_One) REFERENCES
DesignedGroupMembers (GroupID, CallSign),
CallSign_Two VARCHAR(6) NOT NULL,
CONSTRAINT fk_Stations_GroupContacts
FOREIGN KEY (CallSign_Two) REFERENCES
Stations (CallSign),
ContactDate DATETIME NOT NULL,
DurationMinutes SMALLINT,
CONSTRAINT pk_GroupContacts
PRIMARY KEY (GroupID, CallSign_One,CallSign_Two,ContactDate)
);
