I would like some input on a database I am setting up in MySQL 3.23.49 (I have to use this version as it will be hosted on a shared server and that is the latest version they offer)
I am currently working on a web app that users propose events that need to be approved by a supervisor before they show up on the organizations website. Once a proposal has been submitted (and saved in the DB) a supervisor can approve it or disapprove it. If after the proposal has been approved, the user who submitted the proposal wishes to make changes to it, these changes must again be approved by a supervisor. In the meantime, the approved proposal remains on the website as is until the supervisor approves the 'changed' version.
On top of all this I wish to keep a record (or history) of all the stages the proposal has gone through. And one more thing! The proposed events can have 1 or many dates that they occur on.
So far the tables I have come up with are as follows:
eventsTBL ---------------- eventID (PK)
eventProposalsTBL ------------------ proposalID INT (PK) eventID INT (FK) statusID INT (FK) eventName VAR eventDescription TEXT
eventStatusTBL ----------------- statusID INT (PK) statusName VAR
eventDatesTBL ------------------ eventDateID INT (PK) proposalID INT (FK) eventDate DATETIME
Does this seem to be the logical way to set up the DB tables? I know there must be other ways of doing it that I am overlooking and I was wondering if anyone has some other ideas.
Any suggestions/critiques are greatly appreciated!
Previous Topic
