Access07: Hire Co : Create Human Resource tables
|MS Access <|
|Table of contents|
Entity Relationship diagrams
In the design phase the staff/timesheet entities and relationships were described
After discussions with the client, the fully attributed ERD following was created
Note that Primary keys are underlined and foreign keys have a * placed next to them (but only in the diagram - do not put a * in the field name).
Create Staff and Timesheet tables
In the Heights database a single table was created. For the Hire database, 2 tables need to be created: tblStaff and tblTsheet
Create the following 2 tables (Field sizes are shown) The ID fields are the Primary keys
Nte that the order of attributes (fields) is primary key, Foreign key, table attributes (refer to the Timesheet table). This is done so that if you have a lot of fields it is easier to link the fields when creating relationships.
Database Tools > [Show/Hide] Relationships
In the Show Table dialog box either
- double click the two tables (tblStaff and tblTSheet), or
- use the [Control] key to select both table and click [Add]
(Now close the Show Table popup if it is still open)
Resize the fully attributed Entity so that all attributes (fields) show.
Establish the relationship between tblStaff.StfID and tblTSheet.StfID by clicking on tblStaff.StfID and dragging over to tblTSheet.StfID(You should always start from the 1 and drag to the many relationship).
An "Edit Relationships" window opens - select "Enforce Referential integrity"
Select [Join type]
In the "Join Properties" window select the appropriate join type. Here we are selecting type (2) since we want all staff to be included whether or not there is a timesheet. (Normally (1) is selected).
Accept the options to close the pop-ups and you should wind up with the Fully Adttributed ERD as shown following:
Create extended relationships
This is only for advanced users. Suggest you close the database down. Make a copy (using Windows Explorer called dbHireExt.accdb ), then use this to add the extra tables and their relationships.
- To get the 1-1 relationship (tblStaff to tblCarPark) you need to create the StfID field in tblCarPark with the Indexed property set to "Yes (No Duplicates)" this means that a staff member can only ever be entered once into the car park table (that is a staff member can only have one car park)
- To get the Staff - Manager relationship we add tblStaff twice into the Relationship diagram
- When creating attributes use the following order: Primary key(s), Foreign key(s), table attributes
- Always drag from the 1 to the many in a relationship.
- Try to line up dependant fields.
- Always expand tables to show all fields (sometimes this is not possible, but if you have large tables all the dependencies should show dependant fields)
- Rearrange the tables to avoid crossing lines if you need to create many dependencies with many tables.
- Circular relationships may indicate a problem!