Ariadne index page

The Ariadne Data Model

 

 
Contents
1. Introduction
2. Object definition model
3. Conceptual data model
4. Table model
5. Database model
6. Database table descriptions

5. Database model

5.1 Table overview

The database tables are distributed over the modules as follows: (X = in module)

Tables

Current Competition Database

Ariadne Main Module

Archive Database

T010-Remark

X    
T019-Runtype   X  

T020-Run-statustype

X    
T022-Results-statustype   X  
T023-Series-type X    
T024-Sports-discipline X    
T025-Timing-event-status-type   X  
T026-Course-type X    
T027-Run-eventtype   X  
T028-Timing-channel   X  
T029-Race-type X    

T030-Role-type

X    

T031-CU-type

X    

T032-Boat-type

X    

T033-Gender

X    

T040-Category

X    

T044-Age-class

X    

T050-Start-number

X    

T060-Country

X    

T065-Language

X    

T081-SI-temp

X    

T100-Current-competition

X    

T110-Program-Block

X    
T115-Course X    

T120-Gate

X (slalom)    

T200-Person

X

 

 

T210-Address

X    

T220-Official

X    

T230-Participant

X    

T240-Mailing-instruction

X    

T242-Document-type

X    

T243-Document-template

X    

T244-Mail-mode

X    

T246-Mail-destination

X    

T300-Club

X    

T400-Race

X    
T410-Series X (slalom)    

T420-Start

X (marathon)    
T422-Start-event X (marathon)    

T450-Category-in-race

X    

T500-Competing-unit

X    

T510-Run

X    

T512-Run-event

X    

T530-Gate-negotiation

X (slalom)    

T620-Saved-results

X    

T650-Timing-event

X    
T660-Gate-judgement X (slalom)    

T700-Ariadne-system

  X  
T702-Module   X  

T705-Message

  X  
T710-Table   X  
T720-Function   X  
T732-Menu-item   X  
T740-Control   X  
T742-Control-type   X  
T750-Help-page   X  
T805-Archived-competition     X
T805-Archived-result     X
T810-Archived-official     X
T820-Archived-person     X
T830-Archived-club     X
T840-Archived-category     X
T880-Start-interval     X (slalom)
       

5.2. Design & modeling decisions

The database model is designed for implementation in MS Access.

1. (Sep 2002) Per Ariadne version 4.1 Ariadne is split up into the following databases: Ariadne Current Competition (contains all competition data); Ariadne Main (contains reference tables, programs and forms) and Ariadne Archive (contains abstracted data from earlier competitions).

2. (Dec 2001) The initialization function is kept simple: make a copy of a current competition database, rename it, link it under the Ariadne main module, and change the competition parameters to the new competition. (2013) A function is provided to export / import a competition schema to copy over a competition schema (competition, program blocks, races, categories, gates) from a previous competition.

3. (Jun2007) AriadneTime and AriadnePenalty are identical with AriadneAdmin. AriadneTime is to run on the TimePC that also contains the current competition database. AriadneAdmin and AriadnePenalty link via the network to that current competition database. The idea is that when the network fails, AriadneTime can continue processing starts and finishes stand alone.

4. This modeling decision has been removed.

5. This modeling decision has been removed.

6. This modeling decision has been removed.

7. (Feb 2002) After having choosen initially for an T530-Gate-negotiation record per individual gate negotiation, it appeared much more convenient in handling and in presenting, to have all the gate negotiations in one record per run. Space is provided for 25 gates plus T-gate. This taking all gate negotiations per run in one group, imposes no problem of denormalization, because they are always required as one group.

(Jan 2009) Timestamp collection and processing and penalty point collection and processingare parallel processes. T512-Run-event (for timestamp storing) and T530-Gate-negotiation (for penalty points processing) are separate for this reason. The runstatus is distributed over this two record types (i.e. a runstatus for timing and a runstatus for penalty processing). Total runstatus is derived at reporting/query time from timing runstatus and penalty runstatus.

8. This modeling decison has been removed.

9. This modeling decison has been removed.

10. (Feb 2003) The table T620-Saved-results was added. The requirement was that race results which are published, should be 'frozen' somehow. At the GM2002 competition there appeared some differences between the results being published on the publication board, and the overall competition results list (the html-version for being published on the internet). This 'freezing' of published results is realized with putting the results in a separate table after publication. Overwriting race results in this table should only be done on basis of a decision of the chief-judge (for instance after a protest.)

11. (Mar 2003) The table T820-Archived-person is added. In first instance this is a copy of T200-Person. T820-Archived-person provides the person-id to T805-Archived-result and to T810-Achived-official. T200-Person gets a foreign key (archived-person-id) to T820-Archived-person. Via this archived-person-id archived data per person (results and official roles) can be accessed. In this way, persons which are not to be kept in the current-competition, can be deleted, while retaining them in the Archive database. In the archiving function, new persons, added to the current competion are written to the archive.

In the same manner the table T830-Archived-club is added.

12. (May 2003) The table T420-Start is added for Ariadne-Marathon. This table contains starts. With marathon, at one start more races can be started.

13. This modeling decison has been removed.

14. (Dec 2003) The tables T065-Language and T243-Document-template were added to support the generation of language specific emails for entry confirmations. The language is associated to the club, as there is a n:m relation between country and language. From one country two clubs with different languages can participate in the competition, as is the case with Belgium. To associate the language to the club is specific enough (alternative is to associate it to person, or to mailing profile.)

15. (Feb 2004) The table T750-Help-page was added. The table contains the file names of html help pages which can be called from forms to provide help texts for using the form.

16.(Apr 2004) The table T870-Ranking-scale is incorporated into T800-Archived-competition. This because till so far a competition has only one ranking scale. For ease of programming the ranking scale attributes are moved the the T800-Archived-competition table.

17. This modeling decison has been removed.

18 (Apr 2005) Table T730-Menu has been removed. In table T732-Menu-item the attributes menu-1, menu-2 and menu-3 were added. This is a pragmatic solution to make the same menu items selectable in more menu's. (Jan2007) The menu types have been used for configuring the menus for AriadneAdmin, AriadneTime and AriadnePenalty.

19 (Aug 2006) Table T650-Timing-event added to collect the timing messages from the ALGE Timy timeclock .

20 (Nov2008) Kayak Club has been renamed to Club, to make Ariadne more generic, to be used for other sports too.

 

5.3. Ariadne Current Competition Database Model

 

5.3.1 Current competition Database model - Remarks

(1) Same table (multiple depicted for schema convenience)

(2) Remark has relations to Race, Category, Person, Club and System. Remarks are linked to these tables, with a foreign key, which is derived from the primary key of the related table, prefixed with a letter. (R for Race, C for Categories, P for person, K for Club, S for System).

(3) Both Persons and Clubs can have Adresses. Adresses are linked to these tables, with a foreign key, which is derived from the primary key of the related table, prefixed with a letter. (P for person, K for Club).

(5) The n:m relation between Gender and Category is solved by adding a gender = X (mixed).

(8) The sub model around the T620-Saved-results shows as below. T620 is denormalized to ease results queries.

5.3.2. Current Competition submodel mailing profile

In the mailing profile per club is kept which document (invitation, confirmation, results) is to be send to which address (club or official),in which mode (papermail, email, fax). Per document type language specific templates can be used.

The mailing profile is official / person specific, and need to be actualized with each new competition. (e.g. per competition the roles can be fullfilled by other persons, who prefer the mail te be send in other mail modes (email, papermail ).

 

5.4. Ariadne Archive Database Model

(Archive tables in yellow).

5.4.1 Ariadne Archive Database Model - Remarks

-none-

5.5. Ariadne Main System tables Database Model

5.5.1. Ariadne Main Database Model - Remarks

- none -

5.5. Ariadne Marathon Database Model

In Ariadne Marathon the tables related to penalties and gates are removed. A table T420-Start is added which is related to T400-Race:

A start can be a start for more Races. (In a group start competing units of more events can start at the same moment.)

 

Change history

Date Ariadne version Change
Jan2016 5.3 Reviewed and updated
Nov2008 4.6 Reviewed and updated
Jun2007 4.5 Reviewed and updated
Aug2006 4.4 Table T650-Timing-event added
Apr2005 4.3 Table T730 removed & related changes, T702 added
Apr2004 4.2 Table 410-Series changed into T112-Program-block-item, Attributes T870-Ranking-scale moved to T800-Archived-competition, Table T750-Help-pages added, Tables T065-Language and T243-Document-template added.
Nov2003 4.1 Aarchive tables renamed to the 800 tables series, Table T420-Start added for Ariadne Marathon,Table T705-Message added, Table name T720-Operation-type changed to T720-Function, Table T740-Control addedTable, T570-Saved-event-results added (see design decision 10)
Jan2003 3.2 Table T310-category-kayak-club deleted (see design decision 9), Table T240, T242, T244, T246 added for the mailing profile
Sep2002 3.0 'Race' changed in 'Event, databases re-arranged, relation table Competin-unit - person added