New SOTA application

KD9KC’s topic “SOTA surpasses IOTA” became somewhat by diverted examining the authenticity of claimed chaser/activator records resulting from the lack of confirming asterisks which service has been (temporarly) been suspended. (BTW. Frenchmen have a saying: there’s no more permanent state as temporary is! Am I right, Alain - F6ENO? :-))) ) As far as I can concern, this is an issue worth of discussing, therefore I started the present new topic.

I am replying not for X or Y but in general to the debating community. I am not going to blame anybody, who have made their best for our community up to now. However I had been earning my money for over 2 decades from IT system analysis, system design and programming, thus I assume that some of my ideas are worth of considering.

The present solutions really worked (more or less) fine during the passed years… in small DBs with a low number of users. However, by today, both SOTA community and SOTA QSO DB grew to the extent what can not be managed by means of amateur way of thinking and amateur solutions. And what is more, time passing the larger the DB grows, the worse the circumstances will become, because the time and processor power consumption (demand) of processing does not increase linearly with the size of the DB but exponentially! This is predictable, unless we change to professional way of thinking!

What do I mean? There are easy and effective ways as well of obtaining a result. However easy solutions, preferring the ease of coding and testing to long term considerations demand far more cost in general. Nevertheless the seemingly complicated and seemingly more time consuming solutions, may pay off considering long term aspects.

What is my problem? Whenever I start a query (e.g. to see my chaser log), the entire DB is browsed and each QSO is checked against the activator’s log just in order to add that tiny little asterisk… Supposing that I am fool enough to do so a hundred times in a row, that process is re-executed a hundred times and provides me the very same result at a cost of tremendous processor power, slowing down the response of other users! This is absolutely useless wasting of processor power, as it merely results of an operation focusing on the simplicity and ease of data entry forgetting about the costs of the output. (Let me put it again, this way of program organising may have been reasonable and acceptable in case of a low number of users and records.)

What could be done instead? Well, supposing that the data entry (processing of uploaded files or manual entries) were a bit more complex, as a return value the output could be got far easier and faster! The data entry procedure obviously includes several checking procedures, e.g. checks the summit reference specified in the record against the data stored in the reference DB. Why not check the confirmation of the QSO from the partner station as well? Why not adding a “CFM” field into the QSO records and fill it with the good old * or with a Boolean TRUE value in both party’s records, supposing that the coincidence of the rest of the QSO information justifies it? (Of course the one who uploads his data firts, won’t find any confirmation, but the second will. N.b.: Of course this kind of processing assumes that deleting the record from one partner’s log shall also influent the other partner’s record as well, that is delete the * or change TRUE to FALSE value!) Of course this takes a few more instruction, but it fairly pays off!!! Just because there is no need to execute any QSO confirmation checking any more when you ask a “Chaser log” and this obviously accelerates response saving processor power.

This was only a simple example on it, how important the farsighted system design and programming is and how much it influences performance, processor demand, etc.

73: Jóska (M.Sc. E.E.), HA5CW

In reply to HA5CW:

When it comes to computing and the operation of databases my knowledge is very basic, but it seems to me that the procedure that you are suggesting is only of value if both the chaser and the activator enter their logs into the database quite quickly. No doubt many participants do so, but I don’t. I keep a paper log and in order to avoid duplication I enter my log no sooner than when a page is completed, and as I find the process unpleasant and repetitive (for instance a declaration after each entry makes me want to scream!) I sometimes wait as much as several weeks before doing the work. I will leave technical discussions to those with the knowledge.


Brian G8ADD

In reply to G8ADD:
Yes, Brian. Up to you to determine, how do you work. But please consider as follows (I try to put it in a rather plane, easy to understand wording):

For the sake of simplicity let’s assume stations A and B. One of them is the activator and the other the chaser. They established a QSO. Both enters exclusively this single single QSO. The condition of qualifying the QSO confirmed (that is adding * in the query) is the coincidence of QSO records having entered by party A and party B. Right?

But, please note that in case of the existing solution, the confirming * you find in the output is not stored in the DB thus it needs to be processed each and every times you request you log. Right?

The proposed method does the same but slightly different way. Party A enters the record as first. Obviously no confirmation can be made, since party B has not entered his data it yet. Patry B enters his log a few days or weeks later. He already finds the QSO in the DB uploaded by A, thus the contact could be confirmed both for A and B, but it is not in the current application, only in case a query is requested. Right?

And here comes the difference between the two ways of processing: In case the method I propose, the confirming process is performed on the QSO record at the data entry. It is stored in the QSO record, thus it needs not to be checked any more. That is, the entire checking can be ommited, i.e. the consumed processing power can be saved each and every time you ask the query! This would results in shorter response times, thus the clients’ requests were served faster. Just because the cross-checking had been done for (almost) forever, save the case when the confirmation flag needs to be deleted if any of the parties deletes this QSO.

And what is more this confirmation stored in the DB can be applied in any other query, let it be chase unique, chaser log, chaser honor roll or whatever else. That is, the solution simplifies the processing of several program branches. Right?

73: Jóska, HA5CW.

In reply to HA5CW:

What is my problem? Whenever I start a query (e.g. to see my chaser
log), the entire DB is browsed and each QSO is checked against the
activator’s log just in order to add that tiny little asterisk…

I don’t understand how you know this. As far as I know, the presentation of asterisks is still suspended, but even when they were shown, I don’t believe the method used to compute them was ever stated.

Neverthless, your proposal makes perfect sense. Pre-computing the confirmations seems a fairly obvious optimisation. Personally I think I would hold the confirmations in a separate table which could be JOINed with either the activator QSO records or the chaser QSO records, since holding the data in the individual QSO records would break normalisation rules and risk inconsistency.

In reply to M1MAJ:
Dear Martyn!

Thanks for your open-minded approach.

I don’t believe the method used to compute them was ever stated. <<<
My words regarding the topic were based on assumptions and my programmer, system analyst and designer experiences, especially recalling the way how I (also) worked in my early years. But luckily I had good masters!

But Marty, let me go further in order to trigger your or MT’s mind again with another worthy program design and pre-processing tricks obviously paying off: I am sure, the “Chaser log” query computes chaser scores each and every time again and again, making as many seeking operation in the summit DB as many QSOs the log is question comprises.

My issue is the follows: The current data entry procedure procedure checks the summit ID fields against the IDs stored in the summit DB and rejects miss-received or miss-typen ones. Please, note, that this DB contains the number of point values earned for working the summit which won’t alter unless a peak suddenly rises by at least a few meters or a separating valley collapses by at least a few meters. But if so, and the worked XX/YY-xxx summit has already been sought, why not gather more useful info out of that record? Why not open an additional point filed in the QSO record and fill it with the point value from the summit DB? It takes a single instruction to fill it up and a single byte in the DB per QSO records (just like the confirmation flag)! HDs became so cheap by today! Much cheaper than processor time is!

Supposing that my idea were implemented, the query “Chaser log” would not need to bother with summits DB at all any more! Chaser score calculation were simplified to summarising the values stored in the point column of the chaser log table. Of course this would require the programmer to insert a few additional instructions into the current data-entry procedure but as a return value it would eliminate a lot of time consuming seeking on the HD thus it would save not only time but also spare the HW of the hard disc as well. Right?

May you all, friends, have a nice day and work many new summits today!

73: Jóska, HA5CW

In reply to HA5CW:

Joska, in the near future the MT hope to appoint a new database manager, I suggest that it would be a good idea if you discuss your ideas with the new manager after his appointment.


Brian G8ADD

In reply to HA5CW:

It seems to me that there are two flaws in these suggestions.

Firstly, the assumption that confirmation is possible. Who is confirming what?
An activation can not be confirmed because the QSOs don’t have to be with registered Chasers. A successful Chaser contact appears (I am not an authority on the Rules) to require a QSO with an Activator, regardless of whether the activation is successful or not. This can not be checked unless failed activations are registered in some way.

Secondly, the order of entry would be crucial even if the first problem turns out to be less of a problem than it looks to me. Otherwise all the claims would have to be stored until entries confirming them were made and detected; that really would add a serious overhead to database processing.

It also seems to me as a newcomer to SOTA (but not to database management) that SOTA is not competitive in the normal sense and the fact that one certifies one’s entries as honestly made ought to be sufficient.


In reply to M0JLA:

Just a little point, Rod. A failed activation is an activation where no contacts took place. If there are less than four contacts then the activation will appear in the database if uploaded but no score will acrue to the activator.


Brian G8ADD

In reply to M0JLA:

It seems to me that there are two flaws in these suggestions.

I think you have misunderstood them. As I read it, Jóska is not proposing any change of specification that would be externally visible at all. He is simply suggesting that a better internal implementation might be possible.

In reply to HA5CW:

Why not open an
additional point filed in the QSO record and fill it with the point
value from the summit DB?

Remember that there is a relational database behind all of this. One of the design principles of relational databases is that data should be normalised, colloquially referred to as “one fact, one place”. Putting the score value in each QSO record would seriously break normalisation rules.

What’s normally done is to set up a “view” onto the data which turns the structured data into the flattened form which contains multiple copies of information. The SOTA database runs on Microsoft SQL Server, which is an industrial strength database which is actually rather good at this sort of thing. It’s the job of the DBMS to optimise the servicing of queries, which might well involve caching views. I’m not conviced that there is any evidence that keeping the summit point values in a single table would be a problem.

As the person who developed and maintains the SOTA database, I’d like to thank you all for your comments and suggestions.

As Jóska correctly said, the “*” used to be produced everytime a log was displayed by scanning the entire chaser and activations tables for a match against every single QSO. Originally this worked well, but as SOTA expanded, and the size of the SOTA database grew, this took exponentially longer, and was impacting other users of the database server.

However SOTA is not a competitive program, and QSO confirmation is not necessary to claim an award. Although some people find the stars useful, they are not of any real value to the award, so it was decided to remove them until a proper fix can be implemented.

The proposed solution is to add a new table linking the activator and chaser tables when a match is found. I like to keep denormalised data to a minimum, but this seems to be an appropriate use of it.

The table will need to be written just once when the activator or chaser entry is entered into the system. Any Edits and Deletions to either entry would of course have to update the link table. Also a special routine will be necessary to populate the table the first time with the existing matches.

There are several other changes the M.T. and I would like to the database, but as always the problem is finding time to make them. I work for a large I.T. company as a senior software developer, and am 100% busy with that for the forseable future. I have already announced that I will be standing down from SOTA, and handing over the role to someone else with the spare time to continue to develop the database and associated web site.

There are obviously some experienced database people in this thread. If you’d like to help shape the future of SOTA, now could be your chance to get involved - please see for further details.

Gary G0HJQ

In reply to G0HJQ:
Thanks Garry!
I tried to do so even earlier too in order to submit my suggestions straight to the MT but the link you and the topic “Situation Vacant: SOTA Database Administrator” also specified got wrecked at me with a blank white screen. This is another reason, why I started this new topic.
On the other hand “several eyes see more”, we say in HA, that is the more people think about a problem, the more aspacts and potential solutions are considered and as a result of this the end result is likely to become the best. Just like as it is expected to be in case of good team-work.

73: Joska, HA5CW

In reply to G8ADD:


Thank you for pointing out that I did not distinguish between a zero contact activation and an incomplete one. It had not occurred to me that incomplete ones would actually be uploaded (mine have not been). Clearly, if checking is to be carried out a Chaser would rely on such an upload in order to justify the points claimed.


In reply to M0JLA:

That’s right, Rod, I got into the habit of uploading incomplete activations when the stars were still appearing so that the Chasers would have confirmation, and continue in case the star system is resumed. Besides, its nice to have some recognition of your effort even if you don’t get the points!


Brian G8ADD