Results 1 to 5 of 5
  1. #1
    jrock1203 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Location
    Cincinnati
    Posts
    26

    Lightbulb Table design help

    Good morning!

    I've been working on building a QA database for the brewery I work at. We have several sets of data collected for each batch of beer, collected at different points along the brewing process.



    I'm running into issues with table design - which I'm surprised at to be honest.

    The only data point that connects each set of data is a batch ID, so I've chosen that as the primary key.

    The problem is, as you'll see in my attached relationship map, is that I have duplicate columns in each table and I'm not sure the best way to eliminate them.

    Open to all ideas, it's functional as is but will quickly become non-functional.

    Many, many thanks!


    Jeremy


    Click image for larger version. 

Name:	Capture.JPG 
Views:	15 
Size:	163.5 KB 
ID:	34392

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I'm pretty sure that some of us commented on this design being wrong in another thread.
    Your Batch Repository Table should contain the basic top level items for your Batch. FV, Brand, YeastType.

    The other tables should have their own unique ID, with the BatchID as a Foreign Key field in that table.

    Now the second part of the problem. What is actually stored in those other tables? At the moment all we can see are the repeated top level items , so have no idea if you have normalised this extra data or not.
    It looks as if you haven't broken the data down into meaningful chunks, just made a table for each process.
    If those processes / steps /stages are similar they probably belong in one table with a process type identifier.

    I'm skimming the surface a bit here because I don't know your business processes.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'd have a single table for the data points, related one-to-many to the batch table by the batch number. It would have an additional field for what data point it was.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    jrock1203 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Location
    Cincinnati
    Posts
    26
    Minty,

    You're right - apologies. I couldn't figure out how to find my previous post, this thread can be locked.

    Pbaldy, thanks for the input!

  5. #5
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You mean this one https://www.accessforums.net/showthr...72065&p=397740

    It's got some links for design and thoughts from others.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

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

Similar Threads

  1. Replies: 1
    Last Post: 09-19-2016, 12:03 PM
  2. Replies: 3
    Last Post: 04-27-2014, 08:08 PM
  3. Table Design -- want to avoid a design that creates blank fields
    By snowboarder234 in forum Database Design
    Replies: 4
    Last Post: 10-08-2012, 10:13 AM
  4. Replies: 8
    Last Post: 03-24-2012, 11:03 AM
  5. table design: one big table vs. multiple small tables
    By lstairs in forum Database Design
    Replies: 3
    Last Post: 12-31-2009, 08:46 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