Hello, this topic also interests me.
I have been using LOGic for quite some years now. Last year I started to
work on an own database to make it fit my needs even more. As usual though,
it was not completed due to lack of time. I did use MS Access and have no
experience of client/server database. This weekend though, I managed to
setup a MySQL-server and got it working in my home network. I tried the
schema Hamish posted, but it looks like MySQL doesn't support the same
data-types as in Hamish schema (or I'm doing something wrong :)), which
database is Hamish schema for?
I have some comments on the table structures suggested by Hamish (besides
that I didn't get it working, but that was probably more my fault).
In general if we should do this right I think that we need to specify the
purpose of every field in the database. Some fields might of course seem
like obvious at first sight, but if you don't now how the database treats
them then it's easy to screw up your old data... Like prefix in
prefix-table, it's not obvious if that is to be used for every possible
prefix in a country or if it is for the DXCC-prefix for the country (OK it
is if you take a look at the sample data).
Table country:
To use name of the country as primary key will limit the use of the
database. It will not be possible to enter "Comoros" since current
DXCC-prefix is D6. If you are lucky enough you might have the same country
before it was deleted in 1975, then the DXCC-prefix was FH.
Preferred thing would be to use ARRL-code (adifnum) as primary key since
it's unique. But that is not possible since new number is not issued until
a new country has been approved.
I think that the way LOGic is treating it works well. I.e use ARRL-prefix
as primary key. But then you have to add a field dxcc_prefix char(5) to be
able to store ZK1-N and ZK1-S for north and south Cook Island as an
example.
I also suggest that we should add fields: begin_date and end_date
With these fields it should be possible to enter old QSO's for those who
still hasn't entered their old log into the computer. If you check a QSO
date versus begin_date and end_date then you can assign correct DXCC-code
to the QSO even if the country is both current and deleted, like
Y2-callsigns that was used in Germany after East-Germany was "deleted".
Field deleted could then be removed since it is always possible to find out
which date it was deleted. I.e. if end_date is null then the country is not
deleted.
Adifnum, maybe it should be called arrlnum since ARRL is the originator of
the number?
Table continent:
I think that this table can be deleted. Move the field continent to
country-table instead. I can't think of any country that is located in 2
continents. Well except Russia then, but that is anyway separated by
different DXCC-prefix i.e. UA6 and UA9.
Tables cqzone and ituzone could also be deleted. This information is
closely related with the prefix, i.e. prefix in the callsign (not
DXCC-prefix). Move this information to the prefix table. This assumes then
of course that we are going to be able to look up correct country and
zone-information based on a callsign.
>> Nothing wrong with what Hamish posted though...I will try to load it
tonight.
>> Would keying by prefix speed up the looking-up process? (we're more
likely
>> to want to know where '7O' is, or '4W')
I agree, se above.
>True -- but the DXCC list I used didn't unfortunately didn't map
>1:1 from prefix to entity. Some prefixes are used by multiple entities.
>For example VK9Y (from memory), and perhaps also VK0. I was hoping
>to key it all by prefix but that didn't seem possible.
In my example above they would be entered like:
VK0-H Heard Is
VK0-M Macquarie Is
VK9-C Cocos-keeling Is
VK9-L Lord Howe Is
VK9-M Mellish Reef
VK9-N Norfolk Is
VK9-P Papua Terr
VK9-T Terr New Guinea
Note the last entries are deleted which would be marked with 1975-09-15 in
end_date column.
This is by all means not complete, we probably still need another table to
link actual prefixes used with which country but that will be a later
issue... I can probably provide some examples later if someone is
interested and the responses on the above inputs are not completely
rejected.
> I'm interested. The advantage of staying here would be the possibility of
* attracting constructive comments.
Needless to say, I'm also interested to switch to a separate e-mail group
if so is decided.
73 de SM5PPS / Goran
