Results 1 to 9 of 9
  1. #1
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419

    Database Design Best Practice Standard

    Hello,

    I have a table that has two ID field, one is primary and the other one has duplicates.
    I think this table can be made into two as the better database design standard, however I am not sure.

    This is an example of how the table looks.
    Click image for larger version. 

Name:	image1 (2).jpg 
Views:	25 
Size:	89.1 KB 
ID:	37886

    Is this table fine the way it is or it would be more optimal if I break them into two tables as below,
    and can you tell me why so.



    IDa is unique, IDb is duplicates base on the Fruits field.

    Click image for larger version. 

Name:	image2 (1).jpg 
Views:	27 
Size:	97.9 KB 
ID:	37887

    Please advise ! Thx

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Can use normalize and make 1 table with a code and a fruit, no duplicates.
    then another lookup table with 2 fields, no fruit, but all codes. Use this table and join to the fruit table in a query.
    idA, idB

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    John,

    What do the fields IDa and IDb represent? What is difference between nb1 and nb17 where both represent Apple?
    Database structure really requires us to know more about the "business rules" and answers to the above questions.
    Good luck.

  4. #4
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    ranman256,
    So keep table 1 (top one) as how I have it and then put IDa and IDb as a single table.

    Why this way and not the way I have it with the bottom two table. Just want to understand if you don’t mind.

    Orange,
    IDb is systemA’s identification code.
    It’s a one to one relationship with Fruits.

    Or an analogy is a supermarkets product code.

    IDa is more broadly - different system’s code. Is a one to many relationship with fruits being many and IDa being one. a supermarket can get fruits from different distributors even the same fruits.

    IDa an analogy is different distributors code.

    IDb code is burrow from IDa code.
    Some IDa code is not in IDb code.
    All IDb codes are in IDa.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I'm not sure I'm following your response or analogies, but I will make a comment generally.

    In the past when we had multiple sources of "similar" data that had to be processed and/or reported locally, this was the general concept for "local systems with external systems"-

    Create a local unique identifier (in the environment your work in ) using sufficient data from the external system to maintain uniqueness for any inter-environment communication. This became a local to external lookup/reference table.

    So bottom line seems to be:
    What exactly do you need for your work? Do you have to interact/report things back to the external source?
    Do you have to differentiate between nb1 and nb17?

  6. #6
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    Orange,
    Yes we need to know the IDa and IDb to reference back externally so there is a relationship to the external system and data. People know those ID codes base on those external system where it comes from. We preferred not to create new ID.

    ranman256,
    I was thinking using the bottom pics design and not the top pic design (get rid of top pic design). Although I will have more tables but it will be concise. I think.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Perhaps 2 tables for consideration---
    1)LocalToExternal
    OurCode SupID SupCode
    Orange sup1 nb3
    Orange sup1 nb14
    Orange sup2 nm3

    2)ExternalSupplier
    SupID SupName SupOtherDetails
    sup1 GenFood address etc
    sup2 PacificGrocs address etc

  8. #8
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    Orange, thanks for your advise. But how do you know that that is the best practice and it meets standard ?

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    As I said, that is for consideration. In order to be sure - and you know your environment better than readers here - always design, build and test a sample database with realistic test data to ensure the structure and logic match/support your needs.

    See this link for some more info to confirm your analysis - design a data model and work the model with test scenarios.

    Good luck with your project.

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

Similar Threads

  1. Replies: 5
    Last Post: 03-16-2016, 03:56 AM
  2. Best practice for deploying Database
    By saudkazia in forum Access
    Replies: 4
    Last Post: 10-08-2015, 10:46 AM
  3. Best practice - Form design
    By les@wilcock.plus.com in forum Forms
    Replies: 1
    Last Post: 01-02-2015, 05:57 PM
  4. Replies: 1
    Last Post: 08-28-2012, 03:21 PM
  5. Database Structure | Best Practice Question
    By davidson12 in forum Forms
    Replies: 0
    Last Post: 11-05-2009, 03:29 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