Questions on Database Design

[panaretos]

Hi,
New to forum. Beginner/Intermediate Access Experience.

Here is what I am trying to accomplish:

The database i am creating will basically need to have a user check-off a list of Fire Extinguishers that he has checked, on a monthly basis. For example, the user will enter a date, and then a form with a list of all the extinguishers will pop up, and he will place a check mark by each one. When he enters the check mark, that date will be stored so that we have a history of when each extinguisher was checked and by whom (using initials or something)

Currently, my design is simple, something like this:

tblUser
pkUserTableID
fldUserName
fldUserInitial
fldINSPECTIONDATE

tblExtinguisher
pkExtinguisherID
fldExtinguisherNumber
fldExtinguisherManufacturer
fldExtinguisherType
fkUserTableID

My question is this:
-Am I going down the right track with the 2 tables?
-Do I need a third table to Store the Historical Data?
-I am not quite sure how to layout the form so that all the extinguishers are listed.

I know these questions may seem vague, but any help would by highly appreciated.
thank you.

 

[Guus2005]

Question 2: You don't need an extra table for historical data, just add a timestamp or an enddate

 

[oscarooko]

The table tblExtinguisher looks ok. But you might wanna add a field to store the date the extinguisher is checked eg ExtinguisherCheckdate.

 

[oscarooko]

As far as having a different table for historical data, that pretty much depends on how your table will be growing over time. If you feel like the extinguishers checked will be growing too fast, then it might be a good idea to have all records for extinguishers checked be entered into a separate table. This will help improve performance as far as querrying and updating the extinguisher table is concerned.

 

[boblarson]

I think your table structure is just about right, but you should move the inspect date from the user table to a separate junction table (many-to-many). So, like this:

tblUser
pkUserID
fldUserName
fldUserInitial

tblExtinguisher
pkExtinguisherID
fldExtinguisherNumber
fldExtinguisherManufacturer
fldExtinguisherType

tblInspections
pkInspectionID (Autonumber)
fkUserTableID
fkExtinguisherID
InspectionDate