Design for an Archival Description System, Application of ISAD(G): A Study  Appendix 2 Peter Horsman  Implementation Models (Database Tables)  This appendix contains examples of table descriptions, based on the logical model as presented in Appendix 1. The tables are a one-to-one mapping of the entities in the diagram. In a real systems implementation format and size might be changed to meet institutional requirements. As has been discussed in chapter 4, some tables may be combined, or replaced by free-text tables. Appendix 3 contains table descriptions of a more simplified implementation model.  The tables are listed in alphabetical order.  actor actor foundation  aggregation archival item  arrangement business process  business transaction competence custodial fonds custodian  creation function foundation  labour division legal basis  organizational unit person record record keeping  record keeping system series societal function transfer  use     Table: Actor    Column Name Format Size  Remarks Actor_id integer  System generated key  Actor_name text  Name of the actor  Actor_type text  Type of actor (place in the political system; private organization)  Date_begin date  Start date (year)  Date_end date  End date (year)  Description long  Narrative, full description of the actor      Table: Actor Foundation  The table establishes the many-to-many relationship between Actor and the Legal Basis.  Column Name Format Size  Remarks Act_found_id integer  system generated key  Actor_id integer  Foreign key to the Actor  Leg_base_id integer  Foreign key to the Legal Basis  Date_begin date  First date (year) of the Actor foundation  Date_end date  Last date (year)  Description long  full description of the Actor foundation      Table: Aggregation  Classification, part of a Classification scheme, according to which the archival material is/was organized.  Column Name Format Size  Remarks Class_id text  Refers to series  Rec_id integer  Foreign key to Record  Item_id integer  Foreign key to Item  Rec_sys_id integer  Foreign key to the record-keeping system      Table: Archival Item  One might consider one generic table for descriptions of archival materials at all levels. In that case the distinction between Series, and single items could be made via the application (such as forms). This study made the choice to define separate tables, both for the basic units and for the aggregates, the higher constructs.  Column Name Format Size  Remarks Item_id integer  Key. Hidden  Ref_code text  Reference code, given by the archival institution. In a hardcopy output report, such as an inventory, the materials may be renumbered, according to the preference of the archivist  Title text  Short title description of the unit. Usually at the item-level a short title description will be sufficient. Eventually description includes data and physical form. The fields date-begin and date_end may be used only for sorting and selecting. (see remarks in table records).  Date_begin integer  Oldest year of material  Date_end integer  Latest year  Extent text  Physical extension of the item  Contents long  Large description.  Is_Item_in integer  Foreign key to transfer (to link eventually with Custodial Fonds)  Arranged_in integer  Foreign key to Arrangement      Table: Arrangement  The table establishes the many-to-many relationship between the Tables Records and Item, and eventually links this relationship to the record-keeping system.  Column Name Format Size  Remarks Rec_Id integer  Foreign key to Record  Item_id integer  Foreign key to Item  Rec_Sys_id integer  Foreign key to the record-keeping system that has been responsible for the arrangement of the record into the Item  Date_begin date  Start Date (year) of the arrangement  Date_end date  End Date (year) of the arrangement      Table: Business Process (Function)    Column Name Format Size  Remarks Process_id integer  system generated key  Proc_Name text  Short description of the business process  Date_begin date  First date (year) of carrying out the function  Date_end date  Last date (year)  Description long  full description of the function.  Comp_id integer  Foreign key to the competence that translates the societal function to the process      Table: Business Transaction    Column Name Format Size  Remarks Trans_id integer  System generated key  Process_id integer  Foreign key to the Business Process of which the transaction is a part  Trans_Name text  Short description of the transaction  Date_begin date  Start date  Date_end date  End date  Description long  full description of the transaction.      Table: Competence  Generic table, to be used for functions and organizations. Specific forms might be developed  Column Name Format Size  Remarks Comp_id integer  system generated key  Actor_id integer  foreign key to agency to which a function has been attributed  Soc_func_id integer  foreign key to the societal function that has been attributed  Leg_base_id integer  foreign key to the legal basis on which the competence is based  Date_begin date  Date (year) of beginning of the competence  Date_end date  Date (year) of end of the competence  Mandate text  contents of the competence      Table: Custodial Fonds  Highest unit of physical arrangement  Column Name Format Size  Remarks Cust_fonds_id text  Reference code assigned to by the archives  Cust_id integer  Foreign key to the custodian; this may either be an Agency, or an organizational unit.  Title text  Short title description of the Custodial Fonds. Usually at the Fonds-level a short title description will be not sufficient. Eventually description includes data and physical form. The fields date-begin and date_end may be used only for sorting and selecting. (see remarks in table records).  Date_begin integer  Oldest year of material. The field might be calculated, but in order to allow top-down description the field must be enterable.  Date_end integer  Lates year. The field might be calculated, but in order to allow top-down description the field must be enterable.  Extent text  Physical extension of the Fonds. Theoretically the field might be calculated, but in order to justify, or to allow top-down description the field must be enterable.  Contents long  Large description  Part_of integer  Foreign key to higher fonds      Table: Custodian  The table Organizational Unit may be used for Custodian. Eventually columns for address may be added.    Table: Creation  The table establishes a specific Use many-to-many relationship between the tables Records and Business Transaction.  Column Name Format Size  Remarks Rec_Id integer  Foreign key to Record  Trans_id integer  Foreign key to Business Transaction  Pers_id integer  Foreign key to the person which created the record  Date date  Date of creation      Table: Function Foundation  The table establishes the many-to-many relationship between Societal Function and the Legal Basis.  Column Name Format Size  Remarks Func_found_id integer  system generated key  Soc_func_id integer  Foreign key to the Societal Function  Leg_base_id integer  Foreign key to the Legal Basis  Date_begin date  First date (year) of the function foundation  Date_end date  Last date (year)  Description long  full description of the function foundation      Table: Labour Division  The table establishes the many to many relationship between Business Process, and Organizational Unit  Column Name Format Size  Remarks Lab-div_id integer  System generated key  Process_id integer  Foreign key to Business Process  Org_id integer  Foreign key to the organizational unit to which the Process has been assigned  Date_begin date  start date (year)  Date_end date  end date (year)  Description long  description of the labour division      Table: Legal base  legal basis of organization, and/or function, and or/competence.  Column Name Format Size  Remarks Leg_base_id integer  System generated key  Title text  Title (and article) of the law, by-law, statute, or other kind of official document by which the organization was established, or the function to a specific organization mandated or attributed.  Remarks long  Narrative      Table: Organizational Unit    Column Name Format Size  Remarks Org_id integer  System generated key  Org_name text  Name of the organizational unit  Date_begin date  Start date (year)  Date_end date  End date (year)  Description long  Narrative, full description of the organizational unit  Part_of integer  Foreign key to higher organization unit, eventually the Actor      Table: Record  The description of the database table to store records descriptions is rather preliminary, and rather based on paper documents than on electronic documents.  Column Name Format Size  Remarks Rec_id integer  Key. Hidden  Number text  Code, assigned by the archival institution for reference purposes. In a hardcopy output, such as an inventory, the descriptions may be renumbered, according to the preference of the archivist. In the case the output is a retrieval database, the reference code might be unchanged.  Form text  Form of material (diplomatic form)  Contents text  Short description of the unit, eventually the description includes the dates, to cover problems with uncertain dates.. The fields date-begin and date_end may be used only for sorting and selecting, and sometimes been based on interpretation by the archivist.  Date_begin integer  Oldest year of material  Date_end integer  Latest year  Arranged_in integer  Foreign key to Table Arrangement  Created_by integer  Foreign key to the business transaction that created the record.      Table: Record-keeping  Associative table to establish the many to many relationship between the organizational unit responsible for the record keeping system, and the record keeping system itself.  Column Name Format Size  Remarks Rec_sys_id integer  Foreign key to record keeping system  Org_id integer  Foreign key to organizational unit  Description long  Description of the relationship  Date_begin date  Date of begin of responsibility  Date_end date  Date of end of responsibility      Table: Record-keeping system  The system that organizes (or organized) the archival material.  Column Name Format Size  Remarks Rec_sys_id integer  System generated key  Title text  Name of the record-keeping system  Description long  Description of the record-keeping system  Date_begin date  Date of begin of system  Date_end date  Date of end of system      Table: Series  Aggregation of (sub)series, records, or items  Column Name Format Size  Remarks Ref_code  (Class_id)  text Reference code, given by the archival institution. In a hardcopy output report, such as an inventory, the materials may be renumbered, according to the preference of the archivist  Title text  Short title description of the Series. Usually at the Series-level a short title description will be not sufficient. Eventually description includes data and physical form. The fields date-begin and date_end may be used only for sorting and selecting. (see remarks in table records).  Date_begin integer  Oldest year of material. The field might be calculated, but in order to allow top-down description the field must be enterable.  Date_end integer  Latest year. The field might be calculated, but in order to allow top-down description the field must be enterable.  Extent text  Physical extension of the item. Theoretically the field might be calculated, but in order to justify, or to allow top-down description the field must be enterable.  Contents long  Large description.  Is_subseries_in integer  Foreign key to higher series  Arranged_in integer  Foreign key to Arrangement  Sorter Algorithm for sorting Records/Items within a (sub)series      Table: Societal Function  Function to be delivered to society, abstracted from organizational implementation  Column Name Format Size  Remarks Soc_Func_id integer  System generated key  Title text  Description given by law  Narrative long  Description given by archivist      Table: Transfer  The table establishes the many-to-many relationship between the Item and the Custodial fonds.  Column Name Format Size  Remarks Transfer_id integer  System generated key  Item_id integer  Foreign key to the Item  Fonds_id integer  Foreign key to the Custodial Fonds  Transferred-by integer  Foreign key to the transferring agency.  Type  text  Type of transfer (such as custodial action, donation, accrual)  Date date  Date (Year) of transfer      Table: Use  The table establishes a specific Use many-to-many relationship between the tables Records and Business Transaction.  Column Name Format Size  Remarks Rec_Id integer  Foreign key to Record  Trans_id integer  Foreign key to Business Transaction  Pers_id integer  Foreign key to the Person which used the record  Date date  Date of use  back to pre-conference readings page

Published by: Australian Science Archives Project on ASAPWeb
Comments or questions to: ASAPWeb (asapweb@asap.unimelb.edu.au)
Prepared by: Barbara Cytowicz and Lisa Cianci
Graphics by Lisa Cianci
Date modified: 4 March 1998