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
checked - no problem, or
checked - 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.