This is the main record. Every other record is linked to this one via the durc id

members

durc id  INTEGER UNSIGNED ZEROFILL NOT NULL AUTO INCREMENT PRIMARY KEY
surname  VARCHAR(100) NOT NULL DEFAULT ”
name  VARCHAR(100) NOT NULL DEFAULT ”
gender  CHAR(1)
date of birth  DATE
course  VARCHAR(255)
year  ENUM ’JF’

’SF’ ’JS’ ’SS’ ’PG’ ’Staff’ ’Faculty’ ’Alumni’ ’Honorary’ ’Other’ ’unknown’

eye dominance  CHAR(1)
handedness  CHAR(1)
pref air rifle  VARCHAR(5)
pref free rifle  VARCHAR(12)
pref sporter rifle  VARCHAR(5)
pref jacket  TINYINT(2)
pref sling  TINYINT(2)
sling stop  DECIMAL(32)
handstop  DECIMAL(32)
air class  CHAR(1)
free class  CHAR(1)

INDEX name (surname name) INDEX (year)

members phone

durc id  INTEGER UNSIGNED ZEROFILL NOT NULL PRIMARY KEY
phone type  SET ’Term’

’Home’ ’Mobile’ ’Work’ ’other’

phone number VARCHAR(255) NOT NULL

INDEX phone number (phone number)

members address

durc id  INTEGER UNSIGNED ZEROFILL NOT NULL PRIMARY KEY
address type  SET ’Home’

’Term’ ’Work’ ’other’

street address  TEXT
city  TEXT
county  TEXT
country  TEXT

Note that we don’t keep the actual photos in the database, just their location. This way we can copy & modify the pictures with standard programs and the database performance isn’t reduced.

members photo

durc id  INTEGER UNSIGNED ZEROFILL NOT NULL PRIMARY KEY
photo filename  TEXT

One of these tables per discipline tracked to prevent having to use an ENUM with all the various disciplines. This way we can add a club discipline and not have to ALTER TABLEs. Note that we don’t track ten-shot series shot at competitions. There’s probably a better way to track scores : ideas would be appreciated. Anyone got any better ideas ’bout storing sporter scores ? Has anyone got a better idea about actually keeping them ? What if we start attending competitions ?

members scores

members scores air 10m

members scores free 25yd indoor members scores free 50m outdoor

members scores sporter 25yd indoor 4P

durc id  INTEGER UNSIGNED ZEROFILL NOT NULL PRIMARY KEY
date  DATE
witness RO  INTEGER UNSIGNED ZEROFILL NOT NULL
score  SMALLINT UNSIGNED NOT NULL
notes  TEXT

KEY score (score)

KEY witness (witness RO)

members no shows

durc id  INTEGER UNSIGNED ZEROFILL NOT NULL PRIMARY KEY
date  DATE NOT NULL
detail no  TINYINT UNSIGNED ZEROFILL NOT NULL
witness RO INTEGER UNSIGNED ZEROFILL NOT NULL

KEY date (date)

KEY witness (witness RO)

members ids

durc id  INTEGER UNSIGNED ZEROFILL NOT NULL PRIMARY KEY
id type  ENUM ’Student’

’DUCAC’ ’Honorary’ ’Other’ NOT NULL

id number  TINYTEXT
witness RO  INTEGER UNSIGNED ZEROFILL
witness date  DATE

As for members scores, several tables track different ammo types.

members ammo usage

members ammo usage air sport members ammo usage air finalematch members ammo usage winchester members ammo usage eley standard members ammo usage eley club members ammo usage eley match members ammo usage eley tenex

members ammo usage lapua super club

durc id  INTEGER UNSIGNED ZEROFILL NOT NULL PRIMARY KEY
date  DATE NOT NULL
detail no  TINYINT UNSIGNED ZEROFILL NOT NULL
amount issued SMALLINT UNSIGNED ZEROFILL NOT NULL
amount used  SMALLINT UNSIGNED ZEROFILL NOT NULL
issued by RO  INTEGER UNSIGNED ZEROFILL NOT NULL

KEY issued by (issued by) KEY date (date)

duty roster

date  DATE NOT NULL
discipline  ENUM ”Air”

”Free” ”Sporter” ”Event” ”Competition” ”No Shooting” NOT NULL

primary RO  INTEGER UNSIGNED ZEROFILL NOT NULL
secondary RO INTEGER UNSIGNED ZEROFILL NOT NULL
TRO  INTEGER UNSIGNED ZEROFILL NOT NULL
alternate RO  INTEGER UNSIGNED ZEROFILL NOT NULL

INDEX (date)

INDEX (primary RO) INDEX (secondary RO)

INDEX (TRO)

INDEX (alternate RO) INDEX (discipline)

detail

date  DATE NOT NULL
detail no  TINYINT NOT NULL
firing point  TINYINT NOT NULL
durc id  INTEGER UNSIGNED ZEROFILL NOT NULL
confirmed  CHAR(1) NOT NULL DEFAULT ’N’

INDEX (date) INDEX (durc id) INDEX (confirmed)

event

date  DATE NOT NULL
event TEXT

INDEX (date)

competition

date  DATE NOT NULL
competition name  TEXT NOT NULL
competition discipline  TEXT
firing points per detail TINYINT
detail length  TIME
start time  TIME