Results 1 to 10 of 10
  1. #1
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393

    Question Starting a new database

    Hi everyone I am relatively new to creating databases and am just about to delve into my biggest project ever so I thought I would get a few opinions as to the best way to start building my database. I figure the original design will be the most important aspect.

    Essentially I am creating an equipment inspection system whereby inspectors will go to a number of sites and inspect a variety of equipment with the data recorded in the database. In the future I would like to incorporate pdas or mobile equipment to automate the entry process however at the moment I would just like to get the design right.

    As the users of the system will not be programming savvy I would like to make the GUI as user friendly as possible therefore I envisage a switchboard type interface where the user can simply choose add/ edit client, add/ edit site, add/ edit contact, add/ edit inspection, etc.

    This is where my first difficulty exists as it seems I have a number of many to many relationships. For example 1 client can have many contacts and work at many sites, many sites can have many contacts and many clients, etc

    Therefore I have started by creating a client and a contact table and also a clientcontactjunction table. This works fine for referential integrity however not so well for data entry at this stage. As this is going to be a large system I chose this for my first point. Ideally I would like the user to select client from drop down list or add new, from selection it would give a list of sites, from which you could choose inspection. However if a new client is added it should then ask to enter contact details and then site can be selected or new entered, etc

    I can logically code this without the junction table but can't seem to work out how to populate the junction table with the data from the forms, I think subforms and possibly hidden fields may be used.



    I know it is a lot to get head around but if someone understands my system and can suggest if this is correct way of doing or otherwise it would be much appreciated

    Thanks

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Can you give me a brief example of what is in the clientcontactjunction table?

    I'm not sure I understand it [I'm not sure I've used anything like it] and I feel I might be able to help if you could explain your approach in slightly different words - or with an example.

  3. #3
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Hi Robeen. I have looked around and thought this may be best way to link the two tables with a many to many relationship. In the table the only data stored is the ContactID and ClientID primary keys of the other two tables. They are both primary keys of this table and therefore together they make up the primary key. Is this the correct way of doing this? Thanks

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Just taking your example of client table (with a clientID field) and contact table (with a contactID field), though I'd also add a clientlocation table with a locID field, as it seems you have customers with multiple locations.

    So what are your rules?

    For a specific client you can have multiple locations
    For a specific location you can have multiple contacts (contacts can appear for multiple locations, assumes contacts are location specific not client specific)

    If this is the case there are a couple of ways to proceed

    first your LOCATION table will have a CLIENTID field
    Your location table will have a sub table called LocContacts (or something similar) that will have it's own primarykey and a CONTACTID and LOCID field that connect to the contacts table and location table respectively.

    When you're doing data entry your main from will be your customers, your subform will be your locations, you subform under that will be your contacts. If you link them correctly data entry should be pretty minimal.

    Now for the inspection part you would really only need a table that stores the locationID and the fields you use for your inspection becuase you can go back to the location table and look up both the client and the contacts from there. But for the ease of data entry you would include the customerID (combo box or list box data entry)

  5. #5
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Yes you are correct rpeare I will have a location table as well however this will be called site table and unfortunately it is not that easy as each site can not only have multiple contacts it can also have multiple clients. Therefore I cannot put the ClientID in the site table I would have to have a tblClientSiteJunction as well as a tblClientContactJunction and a tblContactSiteJunction and this is only the first group of relationships.

    However I see what you are saying and is it better to have the junction table with just the foreign keys of the tables it is joining and have its own primary key for eg ClientSiteID or ClientContactID than it is the way I was proposing having dual primary keys and have nothing else in the junction table besides the foreign keys of respective tables eg in tblClientSite the only data values would be ClientID and SiteID?

    I will look into the inspection side of things after I get the initial relationships and input working the way I need as it will no doubt get a lot more complex

    Thanks

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Stay away from dual field primary keys if at all possible, you're going to be far better served having a single primary key field for each table (autonumber as you've no doubt made most of yours) If you make a dual value primary key you have to store BOTH of those fields in any child table which means you're carrying a lot of data you just flat out don't need.

    I don't fully understand your relationships between contracts, contacts and locations (sites) but you shouldn't have to record a list of contacts for both customers and sites. You should be able to list sites for each customer, and then have a list of contacts for each site, you shouldn't really need a 'junction' table. If you construct your database this way even if a person is a contact for every site a customer has you're still equipped to handle it.

    I may not have an appreciation for how those three items interact but if you have a set of definable rules it can be set up to follow those same rules.

  7. #7
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Thanks again for your quick reply rpeare

    I understand that ideally I only want one primary key and can probably get away with it in most cases...

    However in the current situation we may have more than one client (customer) at each site, we may have more than 1 contact for each client (multiple sites), therefore we may have more than 1 contact at each site as well.

    I can avoid having dual primary key tables however by having a joined primary key in each junction table such as ClientContactID and ClientSiteID, however these tables will still need to have the foreign keys eg ClientID and SiteID in the table tblClientSiteJunction as well as the ClientSiteID.

    Sorry if this is confusing however they are all many to many relationships

    I really appreciate your input as I want to design this correctly from the start

    Cheers

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Ok I think I understand so you'd probably want a setup like this:

    Code:
    tblCustomers
    CustID CustName ----> other relevant fields
    1      Cust A
    2      Cust B
    
    tblSites
    SiteID SiteName ----> other relevant fields
    1      Site A
    2      Site B
    
    tblContacts
    ContID ContFN ContLN ----> other relevant fields
    1      Mickey Mouse
    2      Donald Duck
    Then, assuming any site can belong to any customer (with multiples possible) you would then need a table

    Code:
    tblCustSite
    CSID CustID SiteID ----> site/customer specific fields
    1    1      1
    2    2      1
    3    2      2
    What I can't tell from your remarks is if the same contact is available for multiple customers. For instance if this were a database for building inspections and you had a supervisor of a building that contained offices for many customers the supervisor would have to be attached to both the customer and the site for that location. If you do you could create a subtable for the site and put all your contacts in there, if the contacts are not site specific but they can still be part of any customer/site combination I would have a sub table:

    Code:
    tblCustSiteSub
    CSCID CSID ContID ----> other relevant information
    That where the CSID would link to the previous table mentioned.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Further to the advice you have gotten from Robeen and rpeare, I recommend you spend some time reading the first few topics in the link below.
    Understanding normal forms and entity relationship diagramming will help you resolve issues in design. Believe me - getting your table structures and relationships correct is key to a successful database project.

    http://www.rogersaccesslibrary.com/forum/topic238.html

    Good luck.

  10. #10
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Thanks repeare yes you are correct I will need junction tables for all levels of site, clients, contacts as none of them are 'one to many' all relationships must be 'many to many'. I just remembered and looked over my databases from uni and they had a dual primary key junction table however the ones I made at my last job actually had a unique junction primary key as you suggest. So the real question was which way is encouraged and is better?

    I think this is the most difficult relationships in my database so I will do it with a unique key for each table and hopefully that makes it easier in the long run

    Thanks for your opinions on the matter

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

Similar Threads

  1. Help starting up a daily entry database
    By sparx in forum Database Design
    Replies: 1
    Last Post: 01-12-2011, 10:56 AM
  2. My head is starting to spin
    By eww in forum Queries
    Replies: 1
    Last Post: 08-30-2010, 02:43 PM
  3. Replies: 7
    Last Post: 05-21-2010, 10:37 PM
  4. Starting an Access database for a group...
    By kkrishna in forum Access
    Replies: 8
    Last Post: 05-19-2010, 05:29 PM
  5. Replies: 9
    Last Post: 04-28-2009, 05:42 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