I am working on an event approval app for an organization. Basically it will work as follows:
1. Users submit event proposals to be approved by supervisors. NOTE: events can have multiple dates/times they occur on.
2. Supervisors can approve event proposals or disapprove and add comments
3. If an event proposal is approved, it shows up in the login area for the marketing dept. (for creating poster and such) and is automatically displayed on the organizations website.
4. If the user who submitted the event wishes to make changes or delete the event, they must submit these changes or delete requests for approval by the supervisor. In the meantime the event still appears on the website as is until the supervisor approves these "change requests" or "deletes"
5. Once the supervisor has approved the changes or delete requests the event info is updated or deleted and the event is flagged in the marketing dept. login area that a change has been made to the event.
In trying to come up with a table design I have come up with this:
eventsTBL (holds the eventID number)
eventProposalsTBL (holds all the data from the proposals)
proposalID INT (PK)
eventID INT (FK)
statusID INT (FK)
active INT (0 or 1)
eventStatusTBL (Holds the status types of proposals - submitted or approved
statusID INT (PK)
proposalDatesTBL (Holds the dates for events to occur on)
eventDateID INT (PK)
proposalID INT (FK)
The way I'm thinking is that I can get:
1. List all approved events on the website by querying the data from the proposals based upon: statusID, active (y/n), latest entry (the dateTimeStamp.
2. To flag events that have approved changes (for the marketing dept.) by querying the data from the proposals based upon: statusID, and how may approved proposal records there are (COUNT). If there are more than 1 flag it.
Does this seem like a logical solution, or am I making things more complicated than they need to be? Any input is greatly appreciated.