Project Management Tables

The following tables do not contain raw data but are required by the data entry system:


Table 'reports'

Function: Holds information about each SEM report, principally the period covered. NB most reports did not cover a calendar year, and several cover periods of more or less than twelve months.
Coverage: All Annual Reports published by the SEM between 1835 and 1880.
Size: 45 records.

Table 'reports'

Field Name Contents Usage
report Report The calendar year in which the first day covered by the report fell. Used to identify the report in other tables.
rep_name Text (16 chars) Standard name for report, for use in output (e.g. '1839/40' or '1853').
start_dy Integer Day within month of start of period covered by report.
start_mn Integer Month of the start of the report.
start_yr Integer Year of the start of the report.
start_date Date Date of first day covered by report, computed from above 3 integers and stored in RDBMS' internal date format.
end_dy Integer Day within month of end of period covered by report.
end_mn Integer Month of the end of the report.
end_yr Integer Year of the end of the report.
end_date Date Date of last day covered by report, computed from above 3 integers and stored in RDBMS' internal date format.
notes Text (128 chars.) This contains notes, e.g. the fact that no copy of the 1870/1 report has been found.


Table 'br_names'

Function: This table is used to look up branch names that appear in the report during data input to find the branch code. It provides alternative versions of branch names and handles any text string used to identify a branch. However, it does not handle all place names that may appear in the SEM data (e.g. destinations of emigrants).
Coverage: All text strings used to identify SEM branches at the start of the branch sections within the Annual Reports that appear in any report up to 1879.
Size: 285 records.

Notes:

  1. The values of 'br_name' are not unique, as there are many examples of a branch closing and then another branch with the same name opening some years later, so to obtain a branch code a check must be made that the relevant report falls in the range first_r <= report <= last_r.
  2. The precise format of some of the values of 'br_name' depended on how they were typed in by the data entry operator.

Table 'br_names':

Field Name Contents Usage
br_key Integer Unique string generated to index the table.
br_name Text (70 characters) The name as entered.
first_r Report The first report in which this branch appears.
last_r Report The last report in which this branch appears.
branch Branch The corresponding code for the branch which appears in the branches table.


Table 'branches'

Function: Holds information about each SEM branch identified by a unique code for each one. The values of branch are, effectively, accession numbers: Liverpool is '1' because it was the first branch, and subsequent branches have higher numbers; when a branch closed, those branches further down the list move up (but while their number in the printed report changes our code number of course stays the same).
Coverage: All branches ever existing in the SEM up to those listed in the 1878 Annual Report.
Size: 129 records.

Notes:

  1. This table was constructed by systematically working through the SEM Annual Reports and includes ALL branches of the union that existed during the study period. This table is used when creating output to replace branch code numbers by the name of the branch, but other information, such as parent and successor branches, could not always be found.
  2. In constructing the table there were a few problematic cases where we had to decide whether a branch which changed its name from one report to the next was in fact the same branch, and others where a branch closed and re-opened shortly afterwards.

Table 'branches':

Field Name Contents Usage
branch Branch Unique identifying code, used to identify the reporting branch in all the data tables. Liverpool = 1 and all subsequent branches get higher numbers in sequence.
br_name Text (30 chars.) This is a 'standard' version of the branch name, to appear in output.
first_r Report First report in which this branch appeared.
last_r Report Last report in which this branch appeared.
town Text (20 chars.) Used in the analysis and, where there was more than one branch in a town, to identify possible branches in linking tramping data, etc.
county Text (20 chars.) Registration county. Used to aggregate branches in the analysis.
gr_east Integer (3-digits) Ordnance Survey grid reference easting (in km); used to locate branches in the analysis.
gr_north Integer (3-digits) Ordnance Survey grid reference northing (in km); used to locate branches in the analysis.
o_day Integer (2-digits) Day when the branch was opened.
o_month Integer (2-digits) Month when the branch was opened.
o_year Integer (4-digits) Year when the branch was opened.
o_date RDB date value Date when the branch was opened; will be null unless day, month and year are all available.
c_day Integer (2-digits) Day when the branch was closed.
c_month Integer (2-digits) Month when the branch was closed.
c_year Integer (4-digits) Year when the branch was closed.
c_date RDB date value Date when the branch was closed; will be null unless day, month and year are all available.
parent Text string (30 chars.) Branch out of which this branch was created', inferred from report. Members transferring from the parent to the child branch in the year of creation will not necessarily have moved home or job.
p_code Integer Branch code of parent branch. Currently unset.
success Text string (30 chars.) Branch to which remaining members transferred on closure, inferred from report. Members transferring from the branch to its successor in the year of closure will not necessarily have moved home or job.
s_code Integer Branch code of successor branch. Currently unset.
notes Text (512 chars.) Comment


(c) Humphrey Southall, 1997


Back to SEM Database Home Page