Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2005

    Request opinions on a database table structure

    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)
    eventID (PK)

    eventProposalsTBL (holds all the data from the proposals)
    proposalID INT (PK)
    eventID INT (FK)
    statusID INT (FK)
    eventName TEXT
    eventDescription TEXT
    dateTimeStamp DATETIME
    active INT (0 or 1)

    eventStatusTBL (Holds the status types of proposals - submitted or approved
    statusID INT (PK)
    statusName TEXT

    proposalDatesTBL (Holds the dates for events to occur on)
    eventDateID INT (PK)
    proposalID INT (FK)
    eventStartDate DATETIME
    eventEndDateTime DATETIME

    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.

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    looks okay to me, except that you don't really need the eventsTBL table, because it holds no data

    also, i would not use eventDateID as the PK of proposalDatesTBL -- instead, i would set the PK as the other three columns

    nice username, by the way -- i grew up in the fifties and i love that music | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts