Proposal to Data Model for a Ham Log running on Linux and a SQL Database
/*==============================================================*/
/* Database name: LOG */
/* DBMS name: ORACLE Version 8 */
/* Created on: 12-05-00 15:52:43 */
/*==============================================================*/
/*==============================================================*/
/* Table : COORDINATES */
/*==============================================================*/
create table HAMLOG_ADM.COORDINATES (
COORD_ID INTEGER not null,
NAME VARCHAR(20),
constraint PK_COORDINATES primary key (COORD_ID)
)
/
comment on table HAMLOG_ADM.COORDINATES is
'List of various coordinates like maidenhead, UMTS, DEG-MIN-SEC etc'
/
/*==============================================================*/
/* Table : CTY_SECTIONS */
/*==============================================================*/
create table HAMLOG_ADM.CTY_SECTIONS (
SECTION_ID INTEGER not null,
NAME VARCHAR(40),
DESCRIPTION VARCHAR(255),
constraint PK_CTY_SECTIONS primary key (SECTION_ID)
)
/
comment on table HAMLOG_ADM.CTY_SECTIONS is
'Various country sections like ITU, DXCC, IOTA etc'
/
/*==============================================================*/
/* Table : RIG_TYPES */
/*==============================================================*/
create table HAMLOG_ADM.RIG_TYPES (
RIG_TYPE_ID INTEGER not null,
TYPE VARCHAR(25),
constraint PK_RIG_TYPES primary key (RIG_TYPE_ID)
)
/
comment on table HAMLOG_ADM.RIG_TYPES i
'All sorts of operating equipment: tranceiver, receiver, antenna, PA etc'
/
/*==============================================================*/
/* Table : QSL_CODES */
/*==============================================================*/
create table HAMLOG_ADM.QSL_CODES (
QSL_CODE VARCHAR(2) not null,
DESCRIPTION VARCHAR(12),
NOTES VARCHAR(255),
constraint PK_QSL_CODES primary key (QSL_CODE)
)
/
comment on table HAMLOG_ADM.QSL_CODES is
'Code for QSL sendt, Received, exchanged, black-list or whatever'
/
/*==============================================================*/
/* Table : RIGS */
/*==============================================================*/
create table HAMLOG_ADM.RIGS (
RIG_ID INTEGER not null,
RIG_TYPE_ID INTEGER not null,
MANUFACTURER VARCHAR(40),
MAKE VARCHAR(40),
AQUIRED_DATE DATE,
DISPOSED_DATE DATE,
FRQ_MIN INTEGER,
FRQ_MAX INTEGER,
NOTES VARCHAR(255),
constraint PK_RIGS primary key (RIG_ID),
constraint FK_RIGS_REFERENCE_RIG_TYPE foreign key (RIG_TYPE_ID)
references HAMLOG_ADM.RIG_TYPES (RIG_TYPE_ID)
)
/
comment on table HAMLOG_ADM.RIGS is
'Rigs of your shack'
/
/*==============================================================*/
/* Table : QSO */
/*==============================================================*/
create table HAMLOG_ADM.QSO (
QSO_NO INTEGER not null,
QSL_CODE VARCHAR(2),
CALLSIGN VARCHAR(15),
NAME VARCHAR(40),
QTH VARCHAR(60),
FRQ_RX INT,
FRQ_TX INT,
"START" DATE,
END DATE,
"MODE" VARCHAR(12),
RPT_TX VARCHAR(8),
RPT_RX VARCHAR(8),
PREFIX VARCHAR(10),
MAIDENHEAD VARCHAR(6),
NOTES VARCHAR(255),
constraint PK_QSO primary key (QSO_NO),
constraint FK_QSO_REFERENCE_QSL_CODE foreign key (QSL_CODE)
references HAMLOG_ADM.QSL_CODES (QSL_CODE)
)
/
comment on table HAMLOG_ADM.QSO is
'Main log book'
/
/*==============================================================*/
/* Table : RIG_QSO */
/*==============================================================*/
create table HAMLOG_ADM.RIG_QSO (
RIG_ID INTEGER not null,
QSO_NO INTEGER,
constraint FK_RIG_QSO_REFERENCE_RIGS foreign key (RIG_ID)
references HAMLOG_ADM.RIGS (RIG_ID),
constraint FK_RIG_QSO_REFERENCE_QSO foreign key (QSO_NO)
references HAMLOG_ADM.QSO (QSO_NO)
)
/
comment on table HAMLOG_ADM.RIG_QSO is
'Linking rigs to a QSO'
/
/*==============================================================*/
/* Table : CTY_LISTS */
/*==============================================================*/
create table HAMLOG_ADM.CTY_LISTS (
CTY_ID INTEGER not null,
PARENT_ID INTEGER,
SECTION_ID INTEGER not null,
PREFIX VARCHAR(10),
NAME VARCHAR(60),
DESCRIPTION VARCHAR(255),
DATE_VALID DATE,
DATE_EXPIRED DATE,
TIMEZONE INT(2),
CONTINENT VARCHAR(2),
constraint PK_CTY_LISTS primary key (CTY_ID),
constraint FK_CTY_LIST_REFERENCE_CTY_LIST foreign key (PARENT_ID)
references HAMLOG_ADM.CTY_LISTS (CTY_ID),
constraint FK_CTY_LIST_REFERENCE_CTY_SECT foreign key (SECTION_ID)
references HAMLOG_ADM.CTY_SECTIONS (SECTION_ID)
)
/
comment on table HAMLOG_ADM.CTY_LISTS is
'Country lists for various award- and organisational lists.
E.g. CQWW, ITU, DXCC, IOTA etc. It may also be used for smaller countries
for regions. Like communes for WANCA.
When several prefixes are present in one country, PARENT_CTY links to
a common country id CTY_ID.'
/
comment on column HAMLOG_ADM.CTY_LISTS.DESCRIPTION is
'Plaintext description of country'
/
comment on column HAMLOG_ADM.CTY_LISTS.DATE_VALID is
'First date country is valid'
/
comment on column HAMLOG_ADM.CTY_LISTS.DATE_EXPIRED is
'Last date country is valid'
/
/*==============================================================*/
/* Table : LOCATIONS */
/*==============================================================*/
create table HAMLOG_ADM.LOCATIONS (
LOCATION_ID INTEGER not null,
CTY_ID INTEGER not null,
COORD_ID INTEGER not null,
VALUE VARCHAR(60),
NAME VARCHAR(60),
constraint PK_LOCATIONS primary key (LOCATION_ID),
constraint FK_LOCATION_REFERENCE_CTY_LIST foreign key (CTY_ID)
references HAMLOG_ADM.CTY_LISTS (CTY_ID),
constraint FK_LOCATION_REFERENCE_COORDINA foreign key (COORD_ID)
references HAMLOG_ADM.COORDINATES (COORD_ID)
)
/
comment on table HAMLOG_ADM.LOCATIONS is 'Position of a QTH'
/
/*==============================================================*/
/* Table : QSO_COUNTRY */
/*==============================================================*/
create table HAMLOG_ADM.QSO_COUNTRY (
CTY_ID INTEGER not null,
QSO_NO INTEGER,
constraint FK_QSO_COUN_REFERENCE_CTY_LIST foreign key (CTY_ID)
references HAMLOG_ADM.CTY_LISTS (CTY_ID),
constraint FK_QSO_COUN_REFERENCE_QSO foreign key (QSO_NO)
references HAMLOG_ADM.QSO (QSO_NO)
)
/
BACK TO DESCRIPTION