Path: csiph.com!usenet.pasdenom.info!news.albasani.net!newsfeed.freenet.ag!news2.euro.net!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail
To: python-list@python.org
From: Dennis Lee Bieber <wlfraed@ix.netcom.com>
Subject: Re: Improve reduce functions of SQLite3 request
Date: Thu, 07 Feb 2013 13:03:23 -0500
Organization: > Bestiaria Support Staff <
References: <mailman.1317.1359915110.2939.python-list@python.org> <keogmb$dml$1@nerdhammel.gnuher.de> <mailman.1331.1360010737.2939.python-list@python.org> <kepbak$ne$1@nerdhammel.gnuher.de> <mailman.1340.1360021639.2939.python-list@python.org> <ket762$vam$1@nerdhammel.gnuher.de> <mailman.1421.1360171591.2939.python-list@python.org> <keup8i$4nd$1@nerdhammel.gnuher.de> <mailman.1439.1360215632.2939.python-list@python.org> <kf03dc$6k6$1@nerdhammel.gnuher.de>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.1454.1360260220.2939.python-list@python.org>
Lines: 220
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:38358

On Thu, 7 Feb 2013 11:33:00 +0000 (UTC), Steffen Mutter
<steffen@webanimations.de> declaimed the following in
gmane.comp.python.general:


> 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" (

	Is that table name specifying a playing season? What happens next
season -- you create a new table and duplicate all the code or edit the
code to use the new name (and lose all access to the previous seasons)?

	The playing season should be a field in the table so that you don't
have to change the application each year.

> "Spiel" INTEGER PRIMARY KEY,
> "Staffel" TEXT NOT NULL,
> "Datum" TEXT,
> "Zeit" TEXT,
> "Halle" INTEGER,

> "Heim" TEXT,
> "Gast" TEXT,
> "HeimNr" INTEGER,
> "GastNr" INTEGER,

	These four items appear, to me, to be a weakness. They should,
(again) to me, be a pair of foreign key references (to the same single
table). (warning, the following is pseudo code, not real SQL -- also,
I'm going to be mixing your German names with English names where I'm
creating something totally new)

	"HeimID" integer foreign key references Teams (ID),
	"GastID" integer foreign key references Teams (ID),

where

create table Teams
(
	ID integer primary key autoincrement,
	Name text,
	Number integer,
	unique index (Name, Number)
)

	You have only one place where a team name&number appears, and a
combination appears only once. This avoids the possibility of someone
changing "Heim", say, but not changing "HeimNr" at the same time. It
also ensures that you don't have different spellings appear for the same
team.

	"Heim" and "Gast" are just the role the teams are taking for that
game; but overall there is no difference between teams, so just one
Teams table. A Teams table could also be expanded to hold information
about where the team is based (a contact address, say); and gives you
something your player list could link to

create table TeamPlayers
(
	ID integer primary key autoincrement,
	TeamID integer foreign key references Teams(ID),
	PlayerID integer foreign key references Players(ID),
	StartDate date	not null	#date the player started on this team
	EndDate date default null	#date player leaves team
)

create table Players
(
	ID integer primary key autoincrement,
	Name	text,
	Address text,
	Phone text,
	otherstuff
)


> "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...

create table PlayerScores
(
	ID integer primary key autoincrement,
	SpielID integer foreign key references Rundes(Spiel),
	PlayerID integer foreign key references Players(ID),
	Score integer not null
)

> 
> 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.

	Just like I did with Teams above -- this should be

	StaffelID integer foreign key references Leagues(ID)

with

create table Leagues
(
	ID integer primary key autoincrement,
	Name text,
	whatever-else-is-needed	#to fully identify the specific league
)

(oh, by the way, I think what you are calling "rows" are normally called
"columns"; a "row" is one record with everything in the schema)

>  Together with 'verband','regional','landes','kreis' you can pick the
> correct position of this special league in the command structure of

	Okay, those are probably the "whatever-else-is-needed".

> 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.

	Since you say "ID-Number", this is probably a candidate for another
foreign key to a table of "Halles" (which could hold a formal name, the
address, etc.)

>  
> 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.

	These all come across as fields that should be handled by the
application, and never entered/changed by users (except the remarks
entry)...

	<snip>

> 
> See above. Use more public keys to encrypt the data and more people can
> see it. 

	Okay -- looking at GPG sites does indicate it can do multi-recipient
encryption -- but that's likely to result in very large blocks of
cypher-text (depending on what it uses internally -- one copy of the
message per recipient, or a smaller one-time key that is encrypted for
each and then used to decrypt the single message.

	I'd still prefer to use, as much as possible, the database system
itself to handle this task...

	Stuff the "private" information into another table, and use an
intersection table to link it to those permitted to see it, and to the
particular game (I could over normalize this, but won't)

create table PrivateViewers
(
	ID integer primary key autoincrement,
	MessageID integer foreign key references Messages(ID),
	ViewerID integer foreign key references Viewers(ID),
	unique index (SpielID, ViewerID)
)

create table Viewers
(
	ID integer primary key autoincrement,
	Name text,
	Role	text		#planner or referee, or player, or unprivileged
)

	Note: "Viewers" may just be your authorized user login system --
somehow you need to get an ID for each user authorized to make changes
to the data

create table Messages
(
	ID integer primary key autoincrement,
	SpielID integer foreign key references Rundes(Spiel),
	Message text
)

	You only allow access to Messages IF the current user is in
PrivateViewers.

select m.Message from Messages as m
	inner join PrivateViewers as pv
		on m.ID = pv.MessageID
	inner join Viewers as v
		on pv.ViewerID = v.ID
where m.SpielID = <desired game ID> 
	and v.Name = <current user name if not identified some other way>
	and v.Role in ("planner", "referee"); #others not allowed access
-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
        wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/