Questions about creating a relational database

Question

This is my first time creating an access database outside of a classroom setting and I am struggling a little bit with creating one for my department at work. Any help would be greatly appreciated.

So I am creating a personnel database that will contain a multitude of information about my employees.  First things first...I am creating tables based on information needed by certain administrative heads in the department.  For instance....one would be specifically a department roster, the next contact info for each member, the third would be career data, etc.  I am a little bit confused about creating relationships.

I want the roster to be the primary table that links the info together.  For instance, the roster table would contain everyone's first, middle, and last name.  I would like to use the last name as a way to track the info on the other tables.  That way, when I begin to input data into the other tables, it is related by last name and can be looked up that way. 

So far, I am unable to create forms that allow you to start inputting data based on a last name from the roster. 

I have tried creating subpages for the roster to do this but I have gotten very confused.

If someone could give me a little bit of step by step for creating the correct relationships, that would be wonderful.  Thank you!

Answer 1

Hi Megan. Last names might not be a good choice to link up multiple tables because it's very possible for more than one person to have the same last name. If you're going to use Access, I would recommend taking advantage of an Autonumber field for your Primary Key. The Autonumber values are guaranteed to be unique, so you can specifically tell which records belong to which person. Just my 2 cents...

Answer 2

Hi MeganMoonshine,

According to your description, you should learn how to decide what information you need, how to divide that information into the appropriate tables and columns, and how those tables relate to each other. So I suggest that you could refer to this article about Database design basics

In addition you could refer to Guide to table relationships

Thanks for your understanding.

Answer 3

Megan,

Typically it is a bad idea to relate tables by a textual field.  Normally each table has a primary key (a simple numeric identifier - in access commonly an AutoNumber) which is used to create relationship between record from different tables.

So your Employee table should look something like

Employees
------------
EmployeeID
FirstName
LastName
DOB
...

Then say you were creating a related their Skillsets you create a Skills table (list of skill to pick) and a EmployeeSkills table

Skills
------
SkillID
SkillName
SkillDescription

EmployeeSkills
-----------------
EmployeeID
SkillID

So you can see how we relate the Employee and Skill by their Primary Keys rather than Names and other textual values.

I'd urge you to look over some one the great resources found at http://www.utteraccess.com/forum/Newcomer-s-Reading-List-t1998783.html

---

Daniel Pineault, 2010-2012 Microsoft MVP
http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 4

As the others have indicated personal names cannot be used as keys, even if combined with other data as a composite key.  I was once present at a clinic where two patients arrived within minutes of each other, both female, both with exactly the same first and last names and both with the same date of birth.  In this context even a composite key of the FirstName, LastName and DoB would not be possible.  This is why each patient has a distinct Hospital Number.

A relational database models each entity type as a table, and each has a primary key.  Where the values can be guaranteed to be distinct, e.g. the names of US states, a 'natural' key can be used, and in some situations this has significant advantages, but it is generally more common to use a 'surrogate' key, usually an autonumber, e.g. EmployeeID.  Each referencing table will then have a similarly named foreign key column of long integer data type, but not an autonumber in this case.

Even where values cannot be guaranteed to be distinct, e.g. city names, it should be possible to define a set of columns as a composite natural key.  Whether this would be appropriate is a matter of judgement in the context of the functionality of the database.  The image below is of two alternative models in my online ComboDemo file which you can find at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169




As you can see the upper set of tables represents a model using surrogate keys, and this would be more common.  The lower set models the same reality using composite natural keys.  This demo is primarily to illustrated the use of correlated combo boxes, and natural keys do make this much easier, as is illustrated in the demo.

The above model is composed entirely of a set of one-to-many relationship types, known formally as  unary relationship types.  A relationship type can be many-to-many, however, and Daniel's reply illustrates this, there being a many-to-many relationship type between Employees and Skills which is modelled by the EmployeeSkills table.  This table resolves the many-to-many relationship type into two one-to-many relationship types, which is how it is always done in a relational database.  In this case it is a binary relationship type as it relates two entity type, but a many-to-many relationship type can relate three, four or more entity types.  These would be tertiary, quaternary relationship types etc.

When it comes to inserting a value into a foreign key in a referencing table, you can still do so by a text value such as a name, even where the foreign key is a surrogate numeric value.  This is done by means of a combo box bound to the numeric foreign key column, but set up to hide the key and show the text value.  In the case of an employee the combo box would be set up as follows:

ControlSource:   EmployeeID

RowSource:     SELECT EmployeeID, FirstName & " " & LastName FROM Employees ORDER BY LastName, FirstName;

BoundColumn:   1
ColumnCount:    2
ColumnWidths:  0cm

If your units of measurement are imperial rather than metric Access will automatically convert the unit of the last one to inches.  The important thing is that the dimension is zero to hide the first column.

Alternatively, you can concatenate the names so that the last name is first, which would be better with a large list of names as it allows the user to enter the initial characters of the last name and progressively go to the first match as each character is entered:

RowSource:     SELECT EmployeeID, LastName & ", " & FirstName FROM Employees ORDER BY LastName, FirstName;

However, this would not enable you to distinguish between two or more employees of the same name, like the two Maggie Taylors I once worked with, so further data should be shown in the combo box's drop-down list to enable a distinction to be made.  In a business environment this could be the employee's job title and department for instance.  You'll find an example in which a person's address is used as the distinguishing factor in the opening form of my NotInList demo in the same OneDrive folder to which I gave a link above.

The above points are really just the nuts and bolts of building a database, however.  While you need to know these sort of things, the most important thing is to have a good understanding of the formal principles of the database relational model itself, so that your database will be an accurate model of the reality with which it is concerned.  A database which accurately models the reality in terms of the real world entity types, the relationships between them and the valid attributes of the entity types, will behave in the same way as the real world, so will be robust and efficient.  Ensuring that this is the case requires a familiarity with concepts like normalization and functional dependency.  This can only be gained by doing the necessary background work.  Attempting to design an operational database without having done the homework on the underlying principles is unlikely to lead to success.

---

Ken Sheridan, Stafford, England