Results 1 to 8 of 8
  1. #1
    creyc is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Mar 2013
    Posts
    6

    Need design help on maintenance database

    I'm trying to come up with a design for a very simple maintenance database to replace my existing slow, buggy, redundant one. (It takes ages just to run a simple query)


    I have a 'CustomerSites' table of my locations which need to be routinely maintained. The fields in this table are: [xCoord], [yCoord], [FirstName], [LastName], [SizeValue]. This data actually comes from a linked CSV file. None of the fields are good candidates for a primary key.

    Then I have a table of historical data collected from visits to sites, the 'Maintenance' table. Fields here are: [xCoord], [yCoord], [MaintDate], [MaintTime], [Tank1Level], [Tank2Level], [Tank3Level], [Tank4Level], [Alarm]. Nothing good to use for primary key here either. All the levels are recorded in numbers, alarm field is Yes/No checkbox, X and Y coords are numbers. Data in this table is generated from a form.


    Here's where I get stuck. The X and Y coords tie the 'sites' and 'maintenance' tables together, but not every record in the 'sites' table has a related record in the 'maintenance' table. (new sites that haven't been visited yet) Likewise, other sites will have many related maintenance records for them. (old sites we've visited countless times) I want to be able to produce a list of every unique X,Y location without duplicates so I can see those records missing Customer information.

    I will then manually track down and populate the missing data with a third table, 'NonCustomerSites'.



    Of course it gets more complicated with dates and times of last maintenance, and average levels for each site, etc, but I can figure these out later. I just want to get a good solid foundation in place and establish the relationships correctly.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Is your current db an Access file? Why would a query be so slow?

    Sounds like the XY fields constitute a compound key. I try to avoid compound keys. Your alternatives are the autonumber field type or to design your own unique ID schema. This means either modifying the CSV file to have ID field or to import the CSV data to Access table.

    Should be possible to produce the summary you describe with your current structure. I don't understand idea of a third table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    creyc is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Mar 2013
    Posts
    6
    It is currently an access file yes, but it takes around a minute to run a query with around 700 records in the CustomerSites table and 1200 records in the Maintenance table. Seems like it should be quicker, but that's still way quicker than doing it by hand.

    I was playing around with compound keys based on X and Y this evening, and I can do this in the CustomerSites table, since there are no duplicate records. But the Maintenance table lists several records for some X, Y coordinates, so I can't use XY as the primary key for that table.

    If I use an autonumber "CustomerID" field as primary, I have no way to keep the two tables coordinated, as I haven't been recording any CustomerID number with the Maintenance records. I'd have to manually go through all my maintenance records and add the appropriate CustomerID wouldn't I?

    As for the third table, I didn't explain it well I suppose. All our active accounts come to me in a CSV file. However we have some maintenance records for sites we don't have accounts with yet. I want the query to also tell me if the XY site is an active account or not. I might be going about this one backwards, I'm still trying to figure out this part.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    That is extremely small database, queries should zip along. If you want to provide db for analysis, follow instructions at bottom of my post.

    Does Maintenance table even need a primary key field? If this table does not have related child tables then PK is not a concern.

    The foreign key field in Maintenance table can be populated with an UPDATE query. After creating the PK in CustomerSites, build query that joins the tables on the existing XY fields. Update the foreign key field with the primary key field. Done, new PK/FK.

    I recommend one table for all accounts.

    How can you have maintenance records for sites you don't have accounts for? This means can't enforce relational integrity on these tables.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    In addition to June's suggestions, a few thoughts :

    CUSTOMERS
    One CUSTOMER can have One or Many SITES.
    One SITE can have One or Many TANKS.

    One Customer's Site today may become another Customer's site tomorrow.
    ________________________________

    tblCustomers
    CustomerID - PK
    FirstName
    LastName
    DateOfRegistration

    The need for this table tblSites is based on the assumption : One Customer's Site today may become another Customer's site tomorrow.
    Assuming, a SITE can be identified by [xCoord] and [yCoord].

    tblSites
    SiteID - PK
    [xCoord]
    [yCoord]

    tblCustomersSites
    CustomersSitesID - PK
    CustomerID - FK
    SiteID - FK
    DateOfSiteRegistration

    tblTanks
    TankID - PK
    TankName
    DateOfTankRegistration
    SiteID - FK

    tblMaintenance
    MaintenanceID - PK
    TankID - FK
    MaintDate
    MaintTime
    TankLevel
    Alarm

    Note : Pardon my habit of ignoring csv, current existing scenario, etc, when it comes to design.

    Thanks

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    In addition to the comments from June7 and recyan, do you have a clear statement of what you are trying to do?
    It seems (at least to me) that you are unclear of your structure, and this seems to be true based on your "slow queries".
    If you could describe your requirement along with a jpg of your current tables and relationships, I'm sure readers can help get your tables set up so that queries and maintenance can be simplified/sped up.

  7. #7
    creyc is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Mar 2013
    Posts
    6
    Quote Originally Posted by June7 View Post
    That is extremely small database, queries should zip along. If you want to provide db for analysis, follow instructions at bottom of my post.

    Does Maintenance table even need a primary key field? If this table does not have related child tables then PK is not a concern.

    The foreign key field in Maintenance table can be populated with an UPDATE query. After creating the PK in CustomerSites, build query that joins the tables on the existing XY fields. Update the foreign key field with the primary key field. Done, new PK/FK.

    I recommend one table for all accounts.

    How can you have maintenance records for sites you don't have accounts for? This means can't enforce relational integrity on these tables.

    Thanks for the input! I suppose the maintenance table doesn't need a primary key, I was just taught you always need one.

    I like the idea of using an update query to create the foreign key for the existing records in the maintenance table, but now going forwards would I have to run that update query again any time I add any new records to the maintenance table? I'm trying not to have to type in a unique ID number when inputting maintenance records using the form, I want to just enter X and Y coords and the recorded data.

    The reason I'm not using one table for all customer accounts is simply because half the records come from a linked CSV file that changes weekly, and I can't modify the CSV file at all. The other half resides in an Access table. And I need to know if the customer information from any given maintenance record is coming in from the CSV file or this third table, I'll call 'StaticCustomers'.


    Quote Originally Posted by recyan View Post
    In addition to June's suggestions, a few thoughts :

    CUSTOMERS
    One CUSTOMER can have One or Many SITES.
    One SITE can have One or Many TANKS.

    One Customer's Site today may become another Customer's site tomorrow.
    ________________________________

    tblCustomers
    CustomerID - PK
    FirstName
    LastName
    DateOfRegistration

    The need for this table tblSites is based on the assumption : One Customer's Site today may become another Customer's site tomorrow.
    Assuming, a SITE can be identified by [xCoord] and [yCoord].

    tblSites
    SiteID - PK
    [xCoord]
    [yCoord]

    tblCustomersSites
    CustomersSitesID - PK
    CustomerID - FK
    SiteID - FK
    DateOfSiteRegistration

    tblTanks
    TankID - PK
    TankName
    DateOfTankRegistration
    SiteID - FK

    tblMaintenance
    MaintenanceID - PK
    TankID - FK
    MaintDate
    MaintTime
    TankLevel
    Alarm

    Note : Pardon my habit of ignoring csv, current existing scenario, etc, when it comes to design.

    Thanks
    Very good point, especially about sites changing hands, that has happened before and caused a few problems. And a site can be identified by its XY coordinates, these are all unique.

    This post gives me a lot to chew through and think about.

    I wish I could post my file but I don't know how to easily strip some sensitive data from it, however I'll be building a new database from these suggestions with mock data.

    Thanks again guys, this is very helpful so far!

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    You can have a field in the customer table for 'source' - CSV or Local - if you want to import the CSV records. Is the CSV cumulative?

    Options for entry of maintenance records:

    1. form/subform arrangement - main form bound to customer, subform bound to maintenance, Master/Child links properties of the subform will synchronize the related records, PK will automatically save to the FK field

    2. form bound to maintenance with combobox to select customer
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 1
    Last Post: 03-12-2013, 02:19 PM
  2. Query for overdue maintenance
    By Rzadziu in forum Queries
    Replies: 4
    Last Post: 01-11-2013, 06:46 AM
  3. Counter Query for Maintenance
    By theperson in forum Queries
    Replies: 3
    Last Post: 10-23-2012, 05:17 PM
  4. Maintenance Database
    By shariq1989 in forum Database Design
    Replies: 1
    Last Post: 06-28-2012, 04:55 PM
  5. Creating a Maintenance Scheduler
    By squirrel in forum Forms
    Replies: 0
    Last Post: 02-15-2006, 03:45 AM

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