0

I'm looking for the best way to store this information. Not every course has an expiry date.

The easiest way I've found so far is:

tblEmployee
-----------
ID (pk)
Expiry1
Expiry2

tblCourseCatalog
----------------
CourseID(pk)
Name

For every course in tblCourseCatalog, a new Expiry is created in tblEmployee to match tblCourseCatalog.CourseID.

I tried to have:

tblCourseExpiryDates
--------------------
EmployeeID (pk) 1:1 with tblEmployee.ID
FirstAid
UnderWaterBasketWeaving

Anytime a new course was added to tblCourseCatalog, a new column was added to tblCourseExpiryDates to match. This became tricky when trying to query some info. Does my current way (Expiry in tblEmployee) change things much from having tblCourseExpiryDates? to me, having a Expiry2 column is a waste if tblCourseCatalog.CourseID=2 (UnderWaterBasketWeaving) does not expire.

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
Scott
  • 37
  • 5

1 Answers1

0

The standard normalised way to store something like this is to have a table where every row looks at just one course employee combination, holds any data that is specific to just that combination, and is usually called something like EmployeeCourse (or CourseEmployee)

EmployeeID
CourseID
ExpiryDate

You only put records in this table where you actually have a date that is valid. For a course that has no dates, no employee would ever get a record. If a given employee has never done a course, they get not record. If you want to remove a date, you can remove the record, or just remove the date but leave the record (I'd probably remove it). If you want to add a new course, you just put a record into the Course table and you're done - you don't have to change anything else.

When you need to look up the record, you need to join to the table with an outer join, so that you get any records in the main table that have no CourseEmployee record.

The downside of this normalised data is that it does make it harder to get a list of all the expiry dates for an employee in one row of output - this is where pivot tables come in (and I'm not sure how they work in access).

If you want to read more about this, look up database normalisation.

DancingFool
  • 1,247
  • 1
  • 8
  • 10