Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2016

    Need help modeling multiple measurement formats while avoiding repeating attributes

    First post, so please be patient ...

    I'm a retired IT professional doing some volunteer consulting for a friend's small manufacturing business. I'm familiar with MS Access, DB2, etc. and have managed data administrators and DBA's, but am a little rusty on getting an effective data model put together. Target database will likely be MS Access with analysis reporting via Excel pivot tables.

    The process I'm modeling involves capturing multiple kinds of quality measurements for a variety of process operations. One operation for an item may require capturing density, weight, a percentage of some sort of content, a qualitative "low, medium, high" opinion, hardness, etc. Another operation may have another entirely different set of measurements (each requiring different formats such as decimal, whole numbers, percentages, textual output, etc.).

    I'm stumped how to model this to support multiple measurement formats without specifying each measurement type as its own attribute (violating 3rd normal form).

    Thanks in advance for any help provided!

    Attached Thumbnails Attached Thumbnails TestDead-EndDataModel.JPG  
    Last edited by BAWard; 02-27-16 at 16:27. Reason: Add attachment

  2. #2
    Join Date
    Feb 2016

    Possible Solution

    Attached is the approach I'm pursuing at this time. It splits the measurements into two different types (numeric and text) and I think it will work for what I'm after.
    Attached Thumbnails Attached Thumbnails PossibleMeasurementDBSolution.JPG  

  3. #3
    Join Date
    May 2016
    Hi BAWard

    For modeling correctly a db (See example on this dbforums post here),

    1. Build a conceptual data model (CDM). The Logical data model is very simple then to generate. With tools, the LDM is automatically built as well as the SQL script for creating the DB
    2. Use dedicated tools which build CDM not only LDM like :
      1. Power*AMC (excellent but expensive)
      2. JMERISE, excellent freeware here

    And of course apply at least the 3 first Normal forms (NF).

    This is a draft of the CDM

    Erratum: A Link must be added between process entity and measure relation - correction on CDM later

    Click image for larger version. 

Name:	mcd.jpg 
Views:	6 
Size:	131.5 KB 
ID:	16973
    But I can't finalize it because:
    • I don't understand relation between a process, a batch, a hierarchy and brand
    • I take hypothesis below, measurements depend on components so independent of a process but is it true????
    Last edited by informer; 06-29-16 at 05:13.

  4. #4
    Join Date
    Feb 2016
    Thanks for response. I found an acceptable solution so no additional replies are needed.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts