Results 1 to 4 of 4
  1. #1
    neo651 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Posts
    110

    Designing a database for a log

    At my company we have a set of things that need to be checked, run, sent out, etc., every morning. I'm currently trying to design a database to manage the log of these items but I'm not sure how to design it. Here's what I've currently setup that I'm doubting.



    There are 3 tables: tbl_Log, tbl_LogItems and tblNotes.

    tbl_LogItems consists of columns ID and LogItem and each entry is one of the items to check every day (34 in total so far)

    tbl_Log's columns are EntryDate and then 34 columns where each is one of the items to check. Each entry is the status of each item for that day.

    tbl_Notes's columns are ID, Note, EntryDate and LogItem. Where EntryDate has a 1 to many relationship with EntryDate from tbl_Log and LogItem has a 1 to many relationship LogItem from tbl_LogItems.

    This design doesn't feel right to me, but the only problem I see is that I have to independently maintain the columns of tbl_Log and the rows of tbl_LogItems even though they're the same thing.

    I hope that's not too confusing.

    Thanks.

  2. #2
    CraigDolphin is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Apr 2009
    Location
    Custer, WA, USA
    Posts
    125
    The way I see it is a bit different.

    Each day you are conducting some kind of checklist of events, right?

    So, there are two main entities that I see there. There's the daily 'CheckEvent', and then there are a list of things to check: I'm going to call them 'CheckItems'.

    So, I would envisage two tables to track these entities, and a third table (a junction table) to show the results for each checkitem on each CheckEvent.

    table CheckEvent
    CheckEventID (auto, pk)
    CheckEventDate (date/time)
    CheckEventNotes (Memo)

    table CheckItems
    CheckItemID (auto, pk)
    CheckItemName (text)

    table CheckEventItems
    CheckEventItemID (auto, pk)
    CheckEventID (fk)
    CheckItemID (fk)
    Status (integer)

    In this structure, there would be 34 rows in the CheckItems table. Not fields/columns: rows.
    Each day you would add a new row to the CheckEvents table, and then 34 rows to the CheckEventItems table (one for each item in the CheckItems table). You could use vba or macro to run an append query to add these rows when needed and inserting the correct CheckEventID value based on the current record. You would then have the user update the status for each item by use of a checkbox, or option group, or whatever is most appropriate.

    HTH

  3. #3
    neo651 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Posts
    110
    Beautiful! Brilliant! Love it! Been setting it up. But I've run into a couple of snags with 2 particular check items.

    One check item was to check that all the security cameras were functional. Originally I was going to have a single check field that allows for multiple values. I know, I know. That's bad form. So I broke the 23 cameras out from one check field to 23 check fields. This will allow us to make individual notes on issue with each camera. Which is great. So I worked through that one on my own.

    But.

    The other check item was related to mobile devices. We track all company cell phones and tablets and every day we need to check that they're all tracking. Again, originally this was one field that allowed for multiple values. Breaking this out into an individual check items for each device is more difficult though because the devices are already entries in a separate linked database. My original design added a field named Tracked into tblMobileDevices. Any device that required tracking simply got a check and that automatically placed that device into a multi-value combo box on a form. This was great because if the employee changed or the device change it was automatically reflected. If I manually add these devices as check fields then anytime a user or device changes I have to update it in two places instead of one. Does that make sense?

    Any ideas on how to get around that?

    Thank you.

  4. #4
    CraigDolphin is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Apr 2009
    Location
    Custer, WA, USA
    Posts
    125
    Well, first things first. 23 check fields? As in columns in CheckEventItems? That's not right. There's one field called status which is your check field.

    If you wish to check each camera individually, then you should be adding 23 camera check ROWS in the CheckItems table, not columns/fields. And then adding all 23 rows to your daily check event along with all the other check items.

    If you wish to add a comments field to the CheckEventItems table that would be fine. Personally, I'd handle this by having a single check event item for 'All Cameras functional' and require the user to input a text explanation if the status field is set to 0/false. But if you want to track each camera individually, then you'd need to add them each as separate rows.

    Your mobile device situation is a bit more difficult. Because you're referencing some items from a local table, and others from a linked table in a separate database, you can't really depend on storing the key field from either, and that makes maintaining referential integrity very difficult.

    If you must track each device individually, then you really do have to source all these items from your local table. This means that you will need to have the ability to manage the data in the CheckItems table from within your other database. You could add this table as a linked table to the other database, and then use dlookups and vba to add rows to the table when the user of the other db checks that a mobile device should be tracked, if that item is not already listed. The question is what happens when the user unchecks that option? If you delete that row from the CheckItems table then you will also be deleting any related rows from the CheckEventItems table too. You may need to disable referential integrity/cascading deletes if this is not what you want to happen. If you do that, you may end up with orphan records in the CheckItemEvents table which could give rise to unexpected results in some forms later on.

    Personally I'd opt to handle it by having a single 'All mobile devices tracked' item, with a required text explaantion if the answer happens to be no.

    It all boils down to what level of tracking is really required.

    Another option would be to create a separate table to track mobile devices separately from other items.

    table CheckEventDeviceStatus
    CheckEventDeviceStatusID (auto, pk)
    CheckEventID (fk)
    MobileDeviceID (fk to the pk in your linked table)
    Status (integer)
    CheckComment (memo)

    So, in this scenario, all the regular check items results would still be stored in CheckEventItems, but the device specific results would be stored in this new table. You'd use another append query to pre-populate the rows for each day's check event, only this append query would be referencing your linked table as its source instead of the CheckItems table.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Please Help! Designing a medical database
    By DrJ in forum Database Design
    Replies: 3
    Last Post: 11-10-2011, 01:27 PM
  2. Need help designing a database for a club
    By tsuchong in forum Database Design
    Replies: 5
    Last Post: 01-31-2011, 12:39 AM
  3. Help with designing database
    By nimalp in forum Access
    Replies: 6
    Last Post: 09-15-2010, 10:34 AM
  4. Designing Load Database
    By salisbut in forum Database Design
    Replies: 5
    Last Post: 08-02-2010, 10:41 AM
  5. questions on designing database
    By schultzy in forum Access
    Replies: 1
    Last Post: 07-18-2009, 03:44 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums