Results 1 to 7 of 7
  1. #1
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142

    Normalization for Table With Types

    Hey! I'm new around here, an amateur access user, and a novice access/database developer. I'm trying to design a database and have a table design question:

    I have a table [Values]. This table contains the individual dollar amounts that make up a whole contract. These individual amounts are broken out for tracking invoicing, and other things.

    The [Values] table has a [ValueTypeID] field, related to a [ValueTypes] table. A "Value" can be of a type Contract, Quote Pending, Budget, Breakout, Work Order, etc. Depending on which type of value a "Value" item is it could have a number of different properties (fields).

    For example, if a value is of the Breakout type it would have a foreign key field to refer another value. But if it were a Work Order type it would have a field for [Date Worked] (among a few others fields), and this information may not be relevant to the other value types.



    What's the best way to approach organizing this data? I assumed that just creating an all the fields for all the possible "properties" a value type might have would bloat the table, many fields would never be used depending on the type. There is also the option of creating a different Value table for each type with its required fields, but that doesn't seem like normalization to me.

    Thank you in advance for your time and any advice you can offer.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Perhaps a related table for the properties. ValueID would be foreign key in this dependent table.

    Is there a limit to how many properties a Value can have?
    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.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Did you mean ValueTypeID? If I understand the relationship and suggestion, won't the property be related to the value type and not the value?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    No, I definitely meant ValueID primary key in table Values would be foreign key in the new table to associate properties with a particular value that has been associated with a contract. Perhaps a better field name would be ContractValueID and a better name for Values table of ContractValues.

    Unless each value has a standard set of properties which never varies regardless of contract, in which case, yes, associate the new table with ValueTypes table.

    For either case, might need a PropertyTypes lookup table.
    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.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Just looking at this briefly and my first impression is that the OP has a number of Contracts. Each Contract may have a variety of "Components"(or subcontracts/phases or similar) for which there are separate attributes. The OP has called these Values and also shows an Amount(which to me signifies $$).
    I think he is looking at Supertype and subtype with Is-a relationship--but that's just my guess based on his comments on different "properties depending on value type". For more info on this see https://www.youtube.com/watch?v=IfaqkiHpIjo (especially about 40 min to end)

    We really need clarification from kd2017 to offer more focused responses.

  6. #6
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by orange View Post
    Just looking at this briefly and my first impression is that the OP has a number of Contracts. Each Contract may have a variety of "Components"(or subcontracts/phases or similar) for which there are separate attributes. The OP has called these Values and also shows an Amount(which to me signifies $$).
    I think he is looking at Supertype and subtype with Is-a relationship--but that's just my guess based on his comments on different "properties depending on value type". For more info on this see https://www.youtube.com/watch?v=IfaqkiHpIjo (especially about 40 min to end)

    We really need clarification from kd2017 to offer more focused responses.
    This youtube lecture is very helpful. It looks like I have the Value supertype with subtypes. I believe exclusive subtypes will be most appropriate for me. I supposes the subtype tables will be one-to-one and can have a ValueID as it's primary key AND the foreign key? Access is letting me do that, but is it good form?

    Also, if i have a series of separate subtype tables is it still necessary to have a ValueType field within the supertype Value table?

  7. #7
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    [double post]
    Last edited by kd2017; 07-23-2017 at 11:26 AM.

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

Similar Threads

  1. Organizing my data in table - normalization?
    By adame in forum Tutorials
    Replies: 8
    Last Post: 06-15-2017, 02:57 PM
  2. Normalization / Table Design
    By Larry in TN in forum Database Design
    Replies: 4
    Last Post: 12-27-2015, 06:04 AM
  3. Replies: 7
    Last Post: 10-25-2011, 08:32 PM
  4. Complex Survey: Table Structure Design and Normalization
    By kevin007 in forum Database Design
    Replies: 2
    Last Post: 07-06-2010, 09:21 AM
  5. Table Normalization Help
    By newhelpplease in forum Database Design
    Replies: 1
    Last Post: 10-15-2007, 09:25 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