Results 1 to 8 of 8
  1. #1
    Join Date
    May 2006
    Posts
    6

    Unanswered: Maintaining Data Consistency

    Is it better to handle the deletion of a record through VBA or through triggers?

    Situation in context of this event:
    Three Tables:
    Code:
    tbl_Sessions
    --
    Session_UID [Primary Key]
    Code:
    tbl_Event
    --
    Event_UID [Primary Key]
    Code:
    tbl_SessionEvent
    --
    SesEve_UID [Primary Key]
    Event_UID [Foreign Key - No Duplicates]
    Session_UID [Foreign Key - Duplicates OK]
    The reason for this design:
    - Sessions can exist independantly from Events
    - Events can exist independantly from Sessions
    - When a Session is assigned to an Event, they maintain a One to One relationship which can only be assigned from the Event dept's Form.
    The trigger for this event:
    If the user decided to either change/remove the session from the event, the record from the tbl_SesEve would be updated/removed.
    Now the question that I am asking, is it better to
    Quote Originally Posted by option 1
    Use a trigger on Update to the record if they decide to change the session; and use a trigger on Update to delete the record from tbl_SesEve if they decide remove the session
    Quote Originally Posted by option 2
    Program in VBA to update the record in tbl_SesEve if they decide to change it and delete the record in tbl_SesEve if the user decides to remove the Session?
    Now, I am also changing the table structure to deal with the issue behind the table data, so if you have any ideas of a design that can handle this criteria, I'm open to hearing it:
    Quote Originally Posted by Context of the design of the table structure
    - Sessions can exist independantly from Events
    - Events can exist independantly from Sessions
    - When a Session is assigned to an Event, they maintain a One to One relationship which can only be assigned from the Event dept's Form.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'm dying to know, what real-world scenario is dictating these requirements?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    May 2006
    Posts
    6
    Quote Originally Posted by Teddy
    I'm dying to know, what real-world scenario is dictating these requirements?
    I'm currently contracted to a organization that arranges events. Two departments are able to create events and host them seperately as well as jointly. One department calls their events, "Sessions"; while the other department calls them, well, "Events".

    The consolidation of two access databases into a solid one database.

    Edit: Solution achieved.
    Last edited by DC_Inc; 05-16-06 at 12:57.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Who cares what "they" call them. You can change how the information is viewed later. Store all the events in one table.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    May 2006
    Posts
    6
    Is that even recommended given this schema:
    Code:
    tbl_event
    --------------------------
    EventID		[PK]
    EventName	
    SessionID	[FK]
    Day	
    Date	
    StartTime	
    EndTime	
    AMBreak	
    PMBreak	
    PostStartTime
    PostEndTime	
    Property		[FK]
    Room		[FK]
    Level		[FK]
    Function		[FK]
    AnticipatedAttendance
    Guarantee	
    MealPrice	
    Tax	
    Gratuity	
    StaffResponsible	[FK]
    RoomSetup	[FK]
    HeadTable	
    Riser	
    DiagramAttached	
    FoodBeverage	
    RecordedAudio	
    RecordedVideo	
    CostCenter1	
    CostCenter2	
    CostCenter3	
    OnSiteContact	
    Notes	
    LastUpdated	
    UpdatedBy
    Code:
    tbl_Session
    --------------------------
    Session_UID	[PK]
    SessionID		
    SessionTitle	
    Day			
    Session_Type	[FK]
    Precis_ID	
    Spkrlvl_UID	[FK]
    PoPP_UID		[FK]
    KnowDom_UID	[FK]
    Track	
    TargAud_UID	[FK]
    ProfTarg_UID	[FK]
    SupTarg_UID	[FK]
    TargOth	
    Day_UID		[FK]
    Date	
    Start_Tm		
    End_Tm	
    Length	
    DelMod_UID	[FK]
    DelMod_Other	
    Perm_RecSess	
    Sssn_Comp	
    Add_Fee	
    Sssn_Desc	
    Lrnr_OutC1	
    Lrnr_OutC2	
    Lrnr_OutC3
    The way they organize these events is that it maintains a one-to-one relationship in the manner of tbl_Events being the parent and the tbl_Sessions being the child to tbl_Events when they are planned together. Otherwise, they are independent of each other.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'm confused, are you designing a database to combine two other active databases or are you designing one database to be used by two organizations? Because if it's the latter, now would be a good time to un-hose your data inconsistency issues instead of slapping two completely unrelated databases into the same mdb and calling it an "integrated solution"...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    And, back to the original question - Access doesn't support triggers. It never has...
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  8. #8
    Join Date
    May 2006
    Posts
    6
    Quote Originally Posted by Teddy
    I'm confused, are you designing a database to combine two other active databases or are you designing one database to be used by two organizations? Because if it's the latter, now would be a good time to un-hose your data inconsistency issues instead of slapping two completely unrelated databases into the same mdb and calling it an "integrated solution"...
    They are related in two main ways:
    - Both of them are pulling from the same pool of speakers
    - Events can host Sessions

    I had reached the solution by setting row source of the Event's Drop Down listing the sessions to through vba:
    Code:
    cmb_Session.RowSource = "SELECT tbl_Session.Session_UID, tbl_Session.SessionID FROM tbl_Session WHERE (((tbl_Session.Session_UID) Not In (select sessionid from tblEvent_II where eventid <> " & Me![EventID] & "))) ORDER BY tbl_Session.Session_UID;"
    I have another issue, but it is for a different thread.

    Thanks anyways.

Posting Permissions

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