Graphical representation of the model
I have access to a proffessional Database Designer tool and has used it to hammer up a few tables for the program. Further I have taken the liberty to name the product LiLog. Probably quite boring. Michelle or Lolita would be more fun, but someone would surely object.
Here follows a few points about the prerequisites for a design.
In a practical logging situation, an operator is not informed on the real identity behind the callsign he is working. One may also argue that it is not relevant to know anything but the callsign. That may be so for a number of operators, but as a mother of all logs, this attitude will severly weaken the design.
You want to know the person for direct QSL or linking two callsigns, one novice and one full to the same person. The callsign is only linked to a person on completing a QSO. The system may suggest who is behind the callsign and often it is probably true.
In my proposal I have made available an optional link to an addressbook I have written which you may find under ../mysys/. This may be used as a callbook.
A prefix is a section of the callsign. Normaly it is the substring from the first character to the first number in the callsign that is not the first character in the string. Sometimes the prefix is the bit that comes before the first /. This proposal has not included rules for prefixes. That will come later, I hope.
Such an area is described by a section. A section is the authority that defines a country. We all know about such authorities like DXCC, IOTA, CQWW, ITU etc. They may wish to divide our planet into zones, countries or whatever.
If this is true, a prefix can be exclusive for a country, several prefixes may be in the same country or a prefix may be shared by the more than one country. Yes, we all know that is so.
One also expects a relationship between different pieces of information. Eg. Prefix, Country and continent. Certain combinations are legal, but others are illegal. LA is for example never in Asia. All theese rules must be maintained in the system.
Such rules may either be maintained as rules in the database or in the application program. When a database holds integrity rules it will decline data and return an error message if you try to violate them. If you maintain integrity in the application you will have to implement the rules every time you edit data. This is often regarded as a less desirable design. You may introduce an error at one of the points of access, or worse, forget to implement it.
In this project we should use technology available to most hams using Linux. That excludes expensive commercial databases like Oracle and Sybase. Postgres, MySQL and MSQL are freely available under a generous lisence. I propose that we make a design that caters for the first two.
A consequence of this is that all integrity rules except primary keys will have to be implemented in the application. Sorry about that.
As you see, it is still under construction. I will discuss what I have done so far.
The most central part of the model is of course the QSO table. That contains the various log entries. Each QSO may be associated with none or one QSL_CODE, i.e. each QSL_CODE may be linked to many QSO's. The QSL code is things like sendt, received, exchanged etc.
A rig in the RIGS table is associated to a rig type. That is e.g. Tranceiver, Antenna etc. The QSO may utilize none or several entries at RIGS. None when the operator does not wish to register a rig on the QSO. Several entries when you wish to register tranceiver, feed line and antenna. To achieve one QSO association to many RIGS and one RIG in many QSO relationship the table RIG_QSO is implemented.
CTY_LISTS contains all the countries for each SECTION (authority). A country may be a part of another country bechause many prefixes may be used in one country. Here you may also put in a section with countries counting for any award. It is in principle no difference with countries for a contest and an award.
There may be several known locations within each country. Each having a coordinate of some sort and a geographical name. The coordinate may be DEG-MIN-SEC, Maidenhead or others. In this model each location may have location value represented in as many coordinate values as you like.
It may be a better idea to get location value in a separate table.
A station worked may represent several countries (one DXCC, one IOTA etc). To allow for one QSO beeing many different countries under different authorities and one country beeing worked by many QSO's the table QSO_COUNTRY should resolve this.
Another set of tables should deal with contests. I have planned the tables CONTEST_LIST, CONTEST_RULES and CONTEST_OPERATORS. This should be a generic system to fit all contests.
Second point is that it is a database and not a file system. That will always be a bit slower. Not necessarily a lot but - slower.
Then you have application programs on top. Communication between database and application comes at a price. It may however be eased by having database and application on different computers. That's what they normally do when 1000+ users are on concurrently.
This log may be used by several users at the same time. Extreemely cool, but a cost. The largest DX-peditions normally brings max 20 members in the team. About 5-7 concurrent users - not a big problem I hope.
A lot of the tables described are admin tables. They are maintained irregulary, and speed is not an issue. Perl or PHP scripts should confortably handle this with straight HTML in Netscape or IE on the LAN. The same goes for post-processing of the log, e.g. ticking off received QSL's
Casual logging may also use straight HTML. Some Java Scripts would be most helpful in making a more efficient user interface.
It is also possible to do high-speed logging in a browser through Java applets. It is however a couple of points to be aware of. Firstly, the various browsers may handle an applet differently. Applets are also executed in a sandbox for security reasons. That means you cannot write to files on local disk or access the RS232 port for communication to your TNC, rig or antenna control through the applet.
It is worth considering running Java programs on the client side. That demands a JRE on the client. Java programs executes slower than C++ programs, but are portable to any platform.
It is of course possible to write your own DOS client. The data are there, so just use them.
The client programs handles the interface to TNC, rig control, antenna rotors, automatic keying etc.
The integrity rules must be implemented in the application as expressed above if you wish to use MySQL. There are also syntactic variations between Oracle and generic SQL.
A number of other tables are also likely to be a part of the system. That remains to be destermened.
http://www.just.me/logand there the common log is up for the entire gig.
If you have any feedback, pros and cons, please bring them to the newsgroup fa.linux.hams so that we may all bring the ideas a bit further.