Results 1 to 11 of 11
  1. #1
    Miskondukt is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    21

    Is my Normalization/Relationships on this DB Design to par w/ best practices?

    Good morning and Lo All,

    I have finally had to graduate into Access/SQL after the sizes of files I am working with are upwards of over 100mbs, in one file it's nearly half a gig. Excel craps out the CPU, thus I am building an Access DB while our IT/CFO determine a BI platform to sign up with (Looker, Tableau).

    I tried building an Access DB over the weekend but when was attempting some queries from multiple tables my values were multiplied by the count of relevant records from other tables. After some more researching I believed the issue was data normalization. I have attached an image of my current DB diagram w/ relationship and was hoping for some input (btw, I am already renaming the fields w/out spaces).

    Thank you all very much for the input.


    kondukt

    Click image for larger version. 

Name:	DatabaseDiagram.png 
Views:	13 
Size:	88.8 KB 
ID:	29171

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    1: data is never repeated on other tables except for primary keys
    2: each table is a set of data tied to one subject
    3: always use the same name for the same field (primary key on table 1 will be called by exactly the same name on table 2)
    4: GLParameters table will need to be changed every year - not good! Add another table which holds PK, MCO, etc. and add a date field for the year
    5: Use straight links - data integrity is essential for a solid database

  3. #3
    Miskondukt is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    21
    Much appreciate the info.

    1: Check
    2: Check
    3: Editing now
    4: Nature of environment for historical data due to a number of acquisitions and GL redesign. We're hoping this is our structure of some time to come.
    5: Straight lines? Can't make everything straight given the nature of where fields are on various tables. Or am I missing something.

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    5: what I meant is that they at first looked like outer joins, but on closer inspection they seem to be inner joins, i.e. data must exist on both tables. That would take care of referential integrity.

  5. #5
    Miskondukt is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    21
    Ah, got it! Thank you for the followup aytee. Rep given

  6. #6
    Miskondukt is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    21
    So if I am getting bad data on queries, would my referential integrity still not be good?

    When I tried to do a sum of "ordertotal" from the RWBillAnalysis table I was getting multiplied results. For instance, I have one record from RWBillAnalysis that has related records of 3 in GLBilling and 5 in GLCOGS. 5*3=15, which is the multiplier for the sum currently displaying on the attached query.

    Click image for larger version. 

Name:	BadQuery2.png 
Views:	11 
Size:	24.5 KB 
ID:	29175

    Click image for larger version. 

Name:	BadQuery.png 
Views:	10 
Size:	13.6 KB 
ID:	29176

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Referential integrity is missing data, for example you would have an order number on the GLBilling table without a matching one on RWBillAnalysis. So no, you RI is good.

    What you have is a one-to-many relationship between these two tables, so in any query you will get the max number of records, in your example it is 3, with all data from the orders repeated each time. Hence your totals not being correct. But why are you totaling it? The table contains one order with one amount(s) which is not going to change no matter what the query does.

    (Note on naming conventions: looking at the top part of your query one does not know if those are queries or tables. It is usual practice to add "tbl", "qry", "frm" etc on to all objects - some people put it in the front, some add it at the end)

  8. #8
    Miskondukt is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    21
    Hrm, you are right on the ordertotal not needing a calc; however, when I pull in the GLBilling and COGS to validate the billing in both our systems and ID cogs those are multiplied by 15 still.

  9. #9
    Miskondukt is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    21
    Err, thank you much for not only confirming the RI is good but adding a quick snippet to ensure I understood its concept.

  10. #10
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Not sure what you are trying to get out of your query results. There are three fields called PostAmount, one on each table. If these contain the exactly the same value (why?) then it will need careful handling to ensure non-duplication. The best way with queries is to be able to verbalize your requirements, if you could do that here we could assist you more accurately.

  11. #11
    Miskondukt is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    21
    Both the GL billing/COGS have multiple records for a single OrderID which is why the sum is needed as those are from our accounting ERP while the RW is our POS/proj mgmt system.

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

Similar Threads

  1. Replies: 20
    Last Post: 01-09-2017, 12:08 AM
  2. Which relationships to use in my design?
    By mbenton in forum Database Design
    Replies: 11
    Last Post: 07-18-2016, 03:51 PM
  3. Normalization / Table Design
    By Larry in TN in forum Database Design
    Replies: 4
    Last Post: 12-27-2015, 06:04 AM
  4. New DB Normalization and relationships
    By Daryl2106 in forum Access
    Replies: 2
    Last Post: 01-06-2013, 12:44 AM
  5. Complex Survey: Table Structure Design and Normalization
    By kevin007 in forum Database Design
    Replies: 2
    Last Post: 07-06-2010, 09:21 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