Changes between Version 3 and Version 4 of statusdatabase

Timestamp:

Aug 8, 2014, 9:57:01 AM (10 years ago)

Author:

tec

Comment:

--

statusdatabase

@@ -3 +3 @@
 The database is made up of 9 tables, joined together by relations. See below for a diagram of the database tables and relations
 
-The PI table is made up of the PI's name, address and their email address. The table has and internal field called 'id' which is filled in automatically and is referred to whenever a PI is linked to a project code.
-||||||||||||= PI Table =||
+The PI table is made up of the PI's name, address and their email address. The table has and internal field called 'id' which is filled in automatically and is referred to whenever a PI is linked to a project code. If you try and delete a PI, and that PI still has project_codes referring to the PI then the Database will prevent you deleting the PI.
+||||||||||||= pi Table =||
 ||= Key =||= Field Name =||= Value Type =||= Constraints =||= Notes =||
-|| Primary Key || id || Integer(4 Bytes) || Required || Value is automatically generated ||
-||  || pi_name || Text (variable length) || Required ||  ||
+|| Primary Key || id || Integer(4 Bytes) ||  Required  || Value is automatically generated. ||
+||  || pi_name || Text (variable length) ||  Required  ||  ||
 ||  || address || Text (variable length) ||  || Not required as may not be known at time of entry. ||
 ||  || email || Text (variable length) ||  || Not required as may not be known at time of entry. ||
 
+The projects table contains all the project codes and what PI relates to which project code. Many project codes can relate to one PI.
+||||||||||||= projects Table =||
+||= Key =||= Field Name =||= Value Type =||= Constraints =||= Notes =||
+|| Primary Key || project_code || Text (variable length) ||  Required  ||  ||
+|| Foreign Key || pi_id || Integer(4 Bytes) ||  Required  || The PI's ID from the pi table. ||
+||  || summary || Text (variable length) ||  ||  ||
+||  || eufar_code || Text (variable length) ||  ||  ||
+||  || objective || Text (variable length) ||  ||  ||
 
-
-
-
-
+The flights table contains the information about one flight. So if two flights were made in one day, then there would be two entries in the flight table. 
+||||||||||||= flights Table =||
+||= Key =||= Field Name =||= Value Type =||= Constraints =||= Notes =||
+|| Primary Key || flight_id || Integer(4 Bytes) ||  Required  || Value is automatically generated. ||
+||  || flight_date || Date || Required || The date that the flight took place ||
+||  || primary_ticket || Integer(2 Bytes) || Required || Primary ticket id for trac ||
+||  || kml_file_location || Text (variable length) ||  ||  ||
+||  || data_location || Text (variable length) ||  ||  ||
+||  || sortie || Text (variable length) || Required || The letter to differentiate multiple flights flown on one day ||
+||  || notes || Text (variable length) ||  ||  ||
+||  || secondary_tickets || Integer(2 Bytes) Array ||  ||  ||
+||  || data_recieved_on || Date ||  ||  ||