Hi Dennis,
I really appreciate your input
I'll confess that I've not looked at any such sites -- mainly
because I wouldn't understand enough about the sport to understand why
one would do something one way or another.
You better do not. The first one I had to use was horrible in design and
there was only one person doing the programming stuff who stopped
development. So the leagues of the 'Badischer Handball Verband' went to
a commercial solution, done by the 'Württembergischer Handball Verband'.
I'm mainly responding from
the point of view of a general software developer with a bit too much
understanding of relational database theory.
Which is fine, I'll get to that aspect later, you showed me a row where
I have a duplicate field, which is not needed at all.
It may not improve initial speed -- but ideally it should in the end
simplify the data you have to maintain. Any time you have a table design
that requires duplicating data from record to record, you run a risk of
inconsistent data on input.
Okay, let's have a look at the scheme.
Every row represents all the data needed to display a single game fully,
here it is:
tables for each season are named like this: Runde[begin-year][end-year]
so:
CREATE TABLE "Runde20122013" (
"Spiel" INTEGER PRIMARY KEY,
"Staffel" TEXT NOT NULL,
"Datum" TEXT,
"Zeit" TEXT,
"Halle" INTEGER,
"Heim" TEXT,
"Gast" TEXT,
"HeimNr" INTEGER,
"GastNr" INTEGER,
"Termin" TEXT, <--- OOPS! Thank you for pointing on the design!!!
"HT" INTEGER,
"GT" INTEGER,
"HP" INTEGER,
"GP" INTEGER,
"melderID" INTEGER,
"bemerkung" TEXT,
"verband" TEXT,
"regional" TEXT,
"landes" TEXT,
"kreis" TEXT,
"schiriID" INTEGER,
"editorID" INTEGER,
"lastchange" TEXT );
The 'Spiel' column holds the number of the game, it's the primary key
which could be used to join data later for a game report, where the
players and the scores they did are inserted by their trainers.
Not implemented yet...
So 'Staffel' = league. There are many similar leagues with the exactly
the same name all over the table, so I need some other rows to get
things sorted correctly.
Together with 'verband','regional','landes','kreis' you can pick the
correct position of this special league in the command structure of
the german handball federation called 'dhb'.
/dhb/shv/baden/karlsruhe/mJC-KL1 is my team I train.
Halle is the ID-Number of
the place where the game is played.
Datum = date, Time = start time of the game.
And you are right, I have done a double insert of those two rows in a
third row: 'Termin' - I should remove this one, because sorting is
easy done by ORDER BY Datum, Zeit - instead ORDER BY Termin, I actually
use.
lastchange represents the last timestamp when the last modification
was done.
melderID represents the one (man or machine), who inserted
the result of the game.
editorID the last real personID who changed
someting.
bemerkung = remarks is for the editor what was edited.
SchiriID is the column which will be changed to TEXT so it can hold
the GnuPG text. This is a required field, depending in how the remark
looks like an icon is displayed to sign the viewer that there is
something he should take care about.
Okay, I see, I have to do some changes:
Remove (Termin) from the table.
Alter schiriID to TEXT the get the GnuPG stuff in there.
(Besides that I remember SQLite does not really care about that)
Here we get to your "alter and modify". The originator (the person
using the "public" key of a key pair) will NOT be able to come back
later and modify the data -- only the owner of the "secret" key can
decrypt it.
I think I will do it this way:
Let's assume the planner for the referees for all
places in 'karlsruhe' logs in.
(Meaning all those places with the Halle ID between 23000-23999 in the
league-area 'karlsruhe' will be provided with referees by him - see, a
lot of data needed and stored in extra tables)
So, as soon as he clicks on the button to do
'referee planning' he will be asked for his private-GnuPG to decrypt
the data.
The data will be displayed with ALL games in those places,
even the games with a higher level he CAN NOT edit - those referee
sets will hold a closed 'lock' as a sign or something like that.
So, let's say he has a complete blank referee section and starts
planning (it will be about August 2013, just to mention) the system
offers him the matching referees who can be used for this game in a
select box.
The referee must match these requirements:
permission to lead he lead this game in hierarchy?
he's not a member of a club involved in this game
can lead only a max. number of 3 games a day
So, he picks a referee, the save button appears in the box. Nothing
happens until he klicks on it to store the data.
When he does, the schiriID TEXT will be encrypted by GnuPG using his AND
the referees public key.
Same thing happens, when a referee logs in and wants to see the games he
was already planned for:
asking for the unlock phrase, accessing the db,
decrypting the data and displaying it.
I have no idea about the performing issue yet - but I think it's worth a
try
I think it will perform nicely for the bhv (country section) maybe for
shv(region section) - but if ALL german referees want to get
their individual games plan and all trainers, players come to my place
too and want it as a PDF file generated. Wooooo....
Disk store caching needed...
You've created a "write once" data system. The only operations the
originator will be able to perform are to delete the record, or replace
the contents blindly (and anyone with delete/replace privileges could do
that unless you implement some sort of database privilege scheme that
doesn't use the PKCS system to lock records to the originator user and
to the destination [reading] user).
See above. Use more public keys to encrypt the data and more people can
see it.
Given those restrictions, you might as well just email the encrypted
data to the reader and forget about storing it anywhere.
That would imply the referees have basic knowledge about PGP or GnuPG
and how to use it. I prefer the way to give them a system, anyone can
use as long as they remember their passwords.
In other words -- you are going to use a single session symmetric
encryption system just to transmit a passphrase to unlock a PKCS key.
AND you want the server keyrings to have both public and secret keys for
each user... If I were paranoid, I sure wouldn't trust that.
That is always the same I run into:
The referee stuff needs to be secure and safe.
But needs to be accessible by the persons who need access to it.
Seems to be a classic 'hen and egg' problem - what's first...
The thing is, that I could give the handball.db to anyone, noone could
get the referee data readable without having the matching private keys.
When PGP
was originally released, the recommendation was that the key-rings
should not even be on the computer hard-drive, but on a floppy that the
user would insert only long enough to perform an encryption/decryption
operation -- and PGP then went and wiped any memory that the key had
passed through. GnuPG, as I recall, is an open source replacement based
on PGP after PGP became a commercial product. Only public keys were ever
given to anyone else.
That is meant to keep your personal data safe like programs, credit
card, bank balances, pictures noone should see - whatever.
Not so for my system I am planning: as soon as the game is played
noone would want to pay the referee for extras