EDIT: also posted on stackoverflow: http://stackoverflow.com/questions/12148715/mysql-database-design-check-in-system
I am attempting to build a check-in system for an office building.
What happens is:
- a Code is called about a bomb threat in building A.
- a designated user is supposed to sweep one or more areas assigned to them.
- a user will then check-in for their area(s) reporting on the status of the area as either
not checked
,checked - no problem
, orchecked - problem
.
I will build an interface that will pull all incoming status reports.
This is the table structure I have so far:
Users
---------------
id | name | username | password
Buildings
---------------
id | name
Sections
---------------
id | building_id | name
Assignments
---------------
id | section_id | user_id
This overall design works to assign users to sections (many to many) and sections to buildings (many to one)
How do I store the status reports while being able to easily retrieve them later.
I want to be able to store status reports in a way that would allow for a user to update an answer, view old answer, and submit a new answer.
Any suggestions are greatly appreciated.