Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142

    Help establish a naming convention

    I've got a few tables at the moment where I want to store a rate, I want to store the numerator and denominator of a rate individually which will make data entry more intuitive. The numerator OR denominator quantity could be in reference to a quantity from a parent table. I'd like some help coming up with an intuitive naming scheme for these two fields.




    Here is a contrived example model:
    Click image for larger version. 

Name:	Untitled.png 
Views:	29 
Size:	12.9 KB 
ID:	49911

    So let's say that I'm trying to record a measurement of 24 linear feet of stud wall with studs 16 inches on center. The record in the measurement table is straight forward and would be 24 feet. The record in the assembly table would be:
    assembly_quantity = 1 stud
    measured_quantity = 1.33 feet
    so my calculated rate is 0.75 studs/foot

    In my reports I would run a calculation to get the total number of studs like so:
    measurement.measured_quantity * assembly.assembly_quantity / assembly.measured_quantity = 24 feet * 1 stud / 1.33 feet = 18 studs

    As you can see both tables have the same field name [measured_quantity]. The numerator and denominator field names in the assembly table or not intuitive, at least not to me.

    How would you go about naming these fields so that when you come back to them in the future that:
    a) it's obvious these two fields work together as ratio
    b) one of the quantities refers to a parent table, in other words the unit of this quantity comes from (or depends on) the unit of the parent table (In this case the denominator, measured_quantity)
    c) the units of the other quantity are independent of any other tables (in this case the numerator, assembly_quantity)

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I'm leaning towards actually storing the calculated rate in one field, and either the numerator or denominator in the other field.

    The rate field would be named something like [assemblies_per_measurement_unit] and the denominator would be named something like [measurement_divisor].

    _per_ and _divisor make it clear that these fields are parts of a ratio (kind of?). Because rate is already calculated, when running reports I can simplify my equations and just use multiplication down the chain of nested relationships, and the divisor field is just used to back into what the user needs for data entry. This means adding code, or a layer of complication, for the data entry part.

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Maybe AssyQtyPK, AssyQtyFK, Length ? Isn't measured quantity just length? Then LengthDesc.

    ID is not a very intuitive name for any field.
    You might be breaking this down too much - not sure.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    In this case the unit of measurement is defined in the measurement table. The next row could be a length, area, or volume measurement.

    But the fields in question aren't key fields, maybe Im not understanding what you're suggesting?

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Suggestion is to not only not use exact same name in multiple tables but to also give ID field a more descriptive name. Such as MeasID_PK and the corresponding foreign key as MeasID_FK and UnitID_PK, UnitID_FK.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Any ideas on the rate fields?

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I mixed up my tables in the response there in trying to comment on the simple field name of ID. In retrospect I'm not sure I see a problem wrt the original question in that those field names seem fairly indicative of what goes in them. Perhaps you're looking for something along the lines of QtyNumerator and QtyDenom.

    There are simple web based calculators for this sort of thing, probably even phone apps that will take your length and spacing and derive the quantity so I think you'd be better off with using one of those. You can even use one for calculating uniform spacing over a length when doing things like deck balusters. For walls, such calculations don't account for king studs or jack studs in openings or the carpenter's methods when it comes to adjoining walls in corners or in the middle of a space for connecting walls. IMO unless this is for something very simple and/or unique it seems that doing a proper take-off with allowances for mistakes is what you ought to be doing, but that's just me.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I'm working on a proof of concept for a much more specialized application, unfortunately web calculators aren't going to cut it. The example model was just a contrived example so as not to distract from my problem. The actual model is much more in depth, which is what probably what had me thinking on establishing a logical naming convention, or pattern, for this early on to make things less confusing for my future self.

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Lack of information is what causes distraction. I once laboured over a solution for shoes. When I posted it, the response was "Well it's not really for shoes so it doesn't work." Total waste of my time.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I do sincerely appreciate your time. With all due respect, it would seem my original post gave too much information such that it lead to distractions, lol

    I suppose just using postfix tags _numerator and _denominator, while not very elegant, gets the job done. k.i.s.s.

  11. #11
    Edgar is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Dec 2022
    Posts
    271
    If you are only going to store the length of the wall and how many studs by foot, why not only one table?
    Code:
    walls: wall_id, title or description, length_in_feet, studs_by_foot, allowance
    if:
    length_in_feet = 24
    studs_by_foot = 1.33
    allowance = 0.03
    Calculation:
    units = length_in_feet * (1/studs_by_foot) * (1 + allowance)
    24 * (1/1.33) * (1 + 0.03) = 18.58 units
    or
    Code:
    walls: wall_id, title or description, length_in_feet, stud_separation_in_feet, allowance
    length_in_feet = 24
    stud_separation = 0.75
    allowance = 0.03
    Calculation:
    units = length_in_feet * (stud_separation_in_feet) * (1 + allowance)
    24 * 0.75 * (1 + 0.03) = 18.54 units

    Look at the calculations, one is the inverse of the other. That can be solved easily with a field where you store whether the values will be multiplied or divided to get the right ratio.

    Still, it is unclear what kind of thing you want to do here. You want to find out how many studs there are in one wall? That's fair, will you later need horizontal elements too?, are they even the same kind of material? how about the frame? how about the windows? how about the places that will require additional support because there's going to be a TV? how about doors? And what are you going to do once you know how many studs? Do you need to have a number of studs? what are you gonna do when the walls are taller? or shorter? it's not the same stud length. You will probably need to account for height here, and type of element. Then you would need to remember they are sold in standard lengths, how are we accounting for that here?

    Perhaps, if you're going to store several types of measurements, you'd need a more universal approach with a table for the project, another for the kind of measurement (concept), another for the type of unit, another for the resource necessary and another for the concept detail. You would then have:
    Code:
    projects: project_id, title, created_at, updated_at
    concepts: concept_id, description, unit, quantity, project_id, created_at, updated_at
    units: unit_id, title, abbreviation, created_at, updated_at
    resources: resource_id, description, unit, created_at, updated_at
    concept_details: concept_detail_id, resource_id, quantity, price is optional, concept_id, created_at, updated_at
    And provide the tools for each type of concept.
    Last edited by Edgar; 03-17-2023 at 05:19 PM. Reason: added details

  12. #12
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by Edgar View Post
    Still, it is unclear what kind of thing you want to do here.
    I'm trying to come up with a naming convention for fields that would clearly and concisely describe them as being two parts of a ratio. Typically one is a quantity of units that references a parent table, the other is a quantity of the subtable. When I come look at this again down the road I want it to be able to deduce based on the names what the fields represent and how they're meant to be used.
    Last edited by kd2017; 03-17-2023 at 08:12 PM.

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Quote Originally Posted by kd2017 View Post
    I'm trying to come up with a naming convention for fields that would clearly and concisely describe them as being two parts of a ratio. Typically one is of a quantity that references a parent table, the other is a quantity of the subtable. When I come look at this again down the road I want it to be able to deduce based on the names what the fields represent and how they're meant to be used.
    A sensible goal. You know your data best. Use whatever makes sense to you.

    But why is measured quantity in both tables?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by June7 View Post
    A sensible goal. You know your data best. Use whatever makes sense to you.

    But why is measured quantity in both tables?
    Because I wasn't creative enough in the moment to decide on something better. That's why I'm here! Lol

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    But why did you show the same name to begin with? Are they the same data? Why duplicate data between tables?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Naming Convention for multi level organization
    By Tuckejam in forum Database Design
    Replies: 6
    Last Post: 04-20-2020, 02:32 AM
  2. Replies: 3
    Last Post: 11-13-2019, 04:30 PM
  3. The PK and FK field naming convention....
    By DBID10T in forum Database Design
    Replies: 6
    Last Post: 10-25-2019, 05:49 AM
  4. Naming convention question? btn or cmd?
    By RLehrbass in forum Access
    Replies: 7
    Last Post: 09-21-2015, 07:51 PM
  5. I use my own VBA naming "convention"
    By keviny04 in forum Modules
    Replies: 13
    Last Post: 06-05-2015, 03:09 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