Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

  1. a Code is called about a bomb threat in building A.
  2. a designated user is supposed to sweep one or more areas assigned to them.
  3. a user will then check-in for their area(s) reporting on the status of the area as either not checked, 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.

share|improve this question
You need to submit this on the Homeland Security StackExchange. HA HA !!! – RolandoMySQLDBA Aug 27 '12 at 19:39
provide the link :P – Mathew A Aug 27 '12 at 19:47
This question would have better traction on StackOverflow because of the app design : stackoverflow.com – RolandoMySQLDBA Aug 27 '12 at 19:52
Okay, I will post on Stack overflow. Thanks! – Mathew A Aug 27 '12 at 19:55
It would be helpful to know if you've tried anything, had problems with a specific design, etc. – Max Vernon Sep 14 '12 at 3:22

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.