Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    carrod65 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    21

    Table Relationships

    Click image for larger version. 

Name:	dfadfdasfdddddddddddddddd.JPG 
Views:	21 
Size:	52.5 KB 
ID:	7014

    Hi All,

    As a access noob, I was hoping someone may be able to give me feedback on my relationships I have set up for the database we are trying to use.

    tableProducts=Information specific to that product number (chemical purity, chemical name, product number, natural or not)
    tableSuppliers=Information specific just to that supplier (Supplier name, contact info, etc)
    tableProducts+Suppliers= Information only specific to the product and supplier such as how that manufacturer produced that chemical, what starting materials used, solvents, etc.


    tableCountry=I just added this because I wanted to create a combo box on the tableProducts+suppliers so who ever is entering data can just select the country of origin instead of typing it (hopefully to avoid improper data)

    In the end I want a form that houses the information for that product (the information from tableProducts) and then either as a subform, or as part of the main form, allows you to pick the suppliers for that product and then will display the information from tableProducts+suppliers.

    Can anyone tell me if these relationships are ok? I think what the tableProducts+suppliers is supposed to be is a Junction table, but as I said I am new with access and not sure if it was setup properly.

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    If a product can be supplied by many suppliers and a supplier can supply many products, then yes, you need the junction table.

    But first, some general recommendations:
    1. Do not use spaces or special characters (+,/,|,\,? etc.) in your table or field names
    2. Use descriptive primary key field names rather than ID use productID in the product table
    3. Do not use lookup fields in your tables. See this site for the problems table level lookups can cause.
    4. Do not use reserved words as table or field names. See this site for a list of Reserved Words & symbols in Access.

    In your junction table you have the field country of origin, you should reference the primary key of the country table: countrycode in the junction table.

    It looks like you repeat several fields in the product table (FG, Natural, Kosher etc.) and the junction table. Why? Duplicating information is a violation of database principles. You'll have to explain further what the data in these fields represent relative to your business rules.

  3. #3
    carrod65 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    21
    Hi JZ,

    Thanks for your feedback.

    You are correct, a product can have any number of suppliers, and any supplier may supply many different products. Thanks for the confirmation that this will need a junction table.

    1. I will clean up the field and Table names
    2. I have changed the primary key to product number and gotten rid of the productID all together
    3. Thank you for the great link. I was not aware of the perils of using the lookup fields. I will remove them and replace them with standard text fields. One question, I did like how using the lookup field limited the selections to the suppliers or products entered in the table, would I just do that with a combo box on a form them?
    4. I will review this list and fix/change any reserved object or field names.

    The next line is not clear to me. The country table has a primary key of CountryCode and that is set up as a one to many relationship with the Country of Origin field on the junction table. Is this what you mean? It seems like you are implying something should be changed.

    The reason you see similar fields in both the products table, and in the products+suppliers table is that this information comes on 2 levels. The product itself might not be kosher as sold by us (it would not be "checked" as kosher in the products tabel), however our supplier of that product may have it cert. Think of it this way, if we use 2 suppliers for product W123456, one of them sells to us as Kosher, the other does not sell to us as Kosher, we cannot sell it as Kosher. The product level (tableProducts) would not have it checked as Kosher, and at the product+supplier level, one entry will me marked as Kosher, and one will be marked as non-Kosher. I hope this is clear.

    I know I am not the most access savvy, so I appreciate you bearing with me as I learn how to set up this database.

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    2. I have changed the primary key to product number and gotten rid of the productID all together
    You could still have the productID as an autonumber primary key. I generally prefer this approach since in general, a primary key should have no significance to the user. A product number probably would. I just meant not to call the field by the name "ID" since it could be confused with the "ID" field of the supplier table. A more descriptive name is generally best.

    Also if the the product number is a text field, Access (and all relational databases) are more efficient with numbers versus text, so having a text field as your primary key might slow up searches.

    3. Thank you for the great link. I was not aware of the perils of using the lookup fields. I will remove them and replace them with standard text fields. One question, I did like how using the lookup field limited the selections to the suppliers or products entered in the table, would I just do that with a combo box on a form them?
    Correct, you would use list and combo boxes on your forms. If you have lookup fields now, it would be best to move the values to a new table and reference the key field of that table as a foreign key.







    This is how I would structure your tables:


    tblProductSuppliers
    -pkProdSuppID primary key, autonumber
    -fkProductID foreign key to tblProducts (long integer number datatype--not text)
    -fkSupplierID foreign key to tblSuppliers (long integer number datatype--not text)
    -fkCountryID foreign key to tblCountry (long integer number datatype--not text)


    Your product table would look like this

    tblProducts
    -pkProductID primary key, autonumber
    -ProductNumber
    -ProductName


    Your supplier table would look like this:

    tblSuppliers
    -pkSupplierID primary key, autonumber
    -txtSupplierName

    Your country table would look like this

    tblCountry
    -pkCountryID primary key,autonumber
    -txtCountryName

    Now a question, can a supplier provide a product that has many countries of origin. For example, the supplier may manufacture the same product in both China and Mexico. If that is the case, your current table structure cannot handle that.

  5. #5
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I thought I would address the following separately

    The reason you see similar fields in both the products table, and in the products+suppliers table is that this information comes on 2 levels. The product itself might not be kosher as sold by us (it would not be "checked" as kosher in the products tabel), however our supplier of that product may have it cert. Think of it this way, if we use 2 suppliers for product W123456, one of them sells to us as Kosher, the other does not sell to us as Kosher, we cannot sell it as Kosher. The product level (tableProducts) would not have it checked as Kosher, and at the product+supplier level, one entry will me marked as Kosher, and one will be marked as non-Kosher. I hope this is clear.
    Having duplicate fields in two tables is not the way to handle this. You would determine the nature of the product you sell, by looking at the product you buy from all possible suppliers. In other words, you would have Access determine the nature of the product. For example, let's say that you buy the same product from 5 suppliers. You can have Access count which suppliers have identified it as kosher if the count is < 5 you know you have to sell it as not kosher.


    Now regarding those fields, they should not be fields but rather records. I assume that those fields represent the attributes of the product as sold to you by the supplier, so a product/supplier combination has many attributes. Since that is a one-to-many relationship, it must be captured in a related table. In fact, you will need 2 tables one for all possible attributes and one for the attributes applicable to the product/supplier combination


    tblAttributes (you would probably have a better name for this table) kosher would be an attribute and I assume that each of the other fields would really each be attributes )
    -pkAttributeID primary key, autonumber
    -txtAttribute

    tblProductSupplierAttributes (this is another junction table)
    -pkProductSupplierAttributeID primary key, autonumber
    -fkProductSupplierID foreign key to tblProductSuppliers
    -fkAttributeID foreign key to tblAttributes

  6. #6
    carrod65 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    21
    Great stuff.

    In regards to your bottom question, we require a definied country of origin for that supplier/product combination. It can change over time, but at any given point our computer systems only allow for one country, so that should be fine the way it is.

    I believe I now have my relationships setup as you have suggested.

    My relationships now look like:
    I feel great about the suggestions you have helped me with.

    Click image for larger version. 

Name:	AAAAA.JPG 
Views:	10 
Size:	62.2 KB 
ID:	7018
    Last edited by carrod65; 04-03-2012 at 01:34 PM. Reason: Poor writing, rewrote for clarification

  7. #7
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    So far so good. I do see the FDA_Reg#. I strongly recommend getting rid of the # sign in the field name. The # sign is used to delimit dates in Access and Visual Basic for Application (VBA) code; it is one of those reserved symbols. What about the attributes I mentioned. I see the fields HACCP, cGMP, solvents_used (if many doesn't that describe a one-to-many relationship), additives_present (another one-to-many?) etc.

  8. #8
    carrod65 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    21
    Quote Originally Posted by jzwp11 View Post
    So far so good. I do see the FDA_Reg#. I strongly recommend getting rid of the # sign in the field name. The # sign is used to delimit dates in Access and Visual Basic for Application (VBA) code; it is one of those reserved symbols. What about the attributes I mentioned. I see the fields HACCP, cGMP, solvents_used (if many doesn't that describe a one-to-many relationship), additives_present (another one-to-many?) etc.
    The solvents_used is typically just one or none, but could possible be more than 1. This is just a text box that the user will enter information such as "extracted with Ethanol" or "toluene and benzene used"

    Same thing for additives, usually none or one chemical added, but this is a text box so they will just put "100ppm BHT", or "Synthetic alpha-Tocopherol at 0.50%".

    Do you see a problem with doing it in that manner? I didn't envision these fields as having relationships at all.

    I missed the # sign in that column, nice catch.

  9. #9
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    The solvents_used is typically just one or none, but could possible be more than 1. This is just a text box that the user will enter information such as "extracted with Ethanol" or "toluene and benzene used"

    Same thing for additives, usually none or one chemical added, but this is a text box so they will just put "100ppm BHT", or "Synthetic alpha-Tocopherol at 0.50%".

    Do you see a problem with doing it in that manner? I didn't envision these fields as having relationships at all.
    Potentially there could be an issue. What happens if one of the additives gets banned by some organization such as ECHA (REACH) in Europe? Wouldn't you want the ability to search for that chemical and identify which products it is in? It is much more difficult to search for specific phrase within a field. It would be better to just have each additive or solvent as a record in a related table. You could have a table of chemicals and categorize each as to whether they are additives or solvents.

    tblChemicals
    -pkChemicalID primary key, autonumber
    -txtChemicalName
    -txtCASNo
    -fkCategoryID foreign key to tblCategory (which would have 2 records additive and solvent)


    tblProductSupplierChemicals
    -pkProdSupChemID primary key, autonumber
    -fkProductSupplierID foreign key to tblProductSupplier
    -fkChemicalID foreign key to tblChemicals

  10. #10
    carrod65 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    21
    The field names may be similar, so that you are thinking they are duplicates, however from our perspective if it is Kosher at the tableProduct level, it is sold BY us as Kosher. If we mark it as Kosher at the tableProducts_Suppliers, that says that is is sold TO us as Kosher (with no guarantee that we actually sell it as Kosher).

    Quote Originally Posted by jzwp11 View Post
    Now regarding those fields, they should not be fields but rather records. I assume that those fields represent the attributes of the product as sold to you by the supplier, so a product/supplier combination has many attributes. Since that is a one-to-many relationship, it must be captured in a related table. In fact, you will need 2 tables one for all possible attributes and one for the attributes applicable to the product/supplier combination
    I don't think we can use the data to determine what we sell it as. I will give an example, we may buy a product from a supplier that is not Kosher, however our Rabbi can evaluate their process and certify it as sold by us as Kosher. In this instance, the supplier_Products table for this combo would not be checked, however the product level could still be Kosher.

    Regarding the Quoted portion of your post, I have read this several times and it isn't registering in my mind. How could Kosher status be a record instead of a field? Each product+supplier combination creates a unique record, and each of these records is either going to be certified Kosher or not.

    In regards to the saying "you will need 2 tables, one for all the possible attributes, and one for the attributes specific to the product/supplier" - Doesn't my tableProducts represent the table for attributes, and the tableProducts_Suppliers represent the table for attributes specific to that supplier and table?

    It looks like you want me to add an additional table, but it just will not sink in my head why this would be necessary. It may be related to my inability to describe how I need it to function.

    Quote Originally Posted by jzwp11 View Post
    tblAttributes (you would probably have a better name for this table) kosher would be an attribute and I assume that each of the other fields would really each be attributes )
    -pkAttributeID primary key, autonumber
    -txtAttribute

    tblProductSupplierAttributes (this is another junction table)
    -pkProductSupplierAttributeID primary key, autonumber
    -fkProductSupplierID foreign key to tblProductSuppliers
    -fkAttributeID foreign key to tblAttributes
    Would't the tblAttributes here serve the same purpse as the tableProducts?

    And on that same note, what would be the advantage of creating a tblProductsSupplierAttributes rather than just using the current tableProducts_Suppliers?

  11. #11
    carrod65 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    21
    Quote Originally Posted by jzwp11 View Post
    Potentially there could be an issue. What happens if one of the additives gets banned by some organization such as ECHA (REACH) in Europe? Wouldn't you want the ability to search for that chemical and identify which products it is in? It is much more difficult to search for specific phrase within a field. It would be better to just have each additive or solvent as a record in a related table. You could have a table of chemicals and categorize each as to whether they are additives or solvents.

    tblChemicals
    -pkChemicalID primary key, autonumber
    -txtChemicalName
    -txtCASNo
    -fkCategoryID foreign key to tblCategory (which would have 2 records additive and solvent)


    tblProductSupplierChemicals
    -pkProdSupChemID primary key, autonumber
    -fkProductSupplierID foreign key to tblProductSupplier
    -fkChemicalID foreign key to tblChemicals
    You raise a great point here. We do have computer systems (SAP) in place and we would be able to pull that information up, this database is more geared for our marketing and quality teams (so if a customer comes and asks a sales rep does product W123456 contain additives and is it made with solvents, they can pull up the product and even with no chemistry background, they would be able to read to the customer this product contains MEHQ at 500ppm and is not produced with any solvents).

  12. #12
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Would't the tblAttributes here serve the same purpse as the tableProducts?

    And on that same note, what would be the advantage of creating a tblProductsSupplierAttributes rather than just using the current tableProducts_Suppliers?
    Since a product/supplier combination can have many attributes that describes a one-to-many relationship. For example, let's say that product A123 from Supplier B is compliant with HACCP and cGMP that would be 2 attributes. Similarly, HACCP compliance may apply to many product/supplier combinations, so another one-to-many relationship. Since we have 2 one-to-many relationships between the same two entities (attributes and product/supplier combinations), we have a many-to-many relationship which requires as before a junction table.

    What I am basically saying is that the attributes should be captured as records not as fields within a table. What would happen if you added a new attribute? You would have to alter your table structure and every associated form, query or report. With the approach I describe, you would just add a new attribute record to the attribute table and then associated that attribute to the applicable product/supplier combination (via a record in tblProductSupplierAttributes).

    Just for something else to consider. Since your company also supplies products, wouldn't your company be included in the supplier table and your products included in the product table with those of your suppliers? You would then associate your products to your own company in the product/supplier junction table and for that matter, you can then associate the applicable attributes to your product. All of this can be done within the same group of tables already described. Also, if you use solvents or additives in making your products, you can capture that as well.

  13. #13
    carrod65 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    21
    Quote Originally Posted by jzwp11 View Post
    Since a product/supplier combination can have many attributes that describes a one-to-many relationship. For example, let's say that product A123 from Supplier B is compliant with HACCP and cGMP that would be 2 attributes. Similarly, HACCP compliance may apply to many product/supplier combinations, so another one-to-many relationship. Since we have 2 one-to-many relationships between the same two entities (attributes and product/supplier combinations), we have a many-to-many relationship which requires as before a junction table.

    What I am basically saying is that the attributes should be captured as records not as fields within a table. What would happen if you added a new attribute? You would have to alter your table structure and every associated form, query or report. With the approach I describe, you would just add a new attribute record to the attribute table and then associated that attribute to the applicable product/supplier combination (via a record in tblProductSupplierAttributes).

    Just for something else to consider. Since your company also supplies products, wouldn't your company be included in the supplier table and your products included in the product table with those of your suppliers? You would then associate your products to your own company in the product/supplier junction table and for that matter, you can then associate the applicable attributes to your product. All of this can be done within the same group of tables already described. Also, if you use solvents or additives in making your products, you can capture that as well.

    When you refer to a "record" that makes me think of what would be referred to as a row in excel (I know I can't think of Access in terms of excel, but just for clarification reasons) or a unique bunch of related data. In the screenshot of my table, all of the attributes specific to that supplier and product combination were setup as fields. Given that I want to either have HACCP checked for every supplier/product combo, how would I put HACCP as a record? It may just be my lack of Access jargon, but if I put HACCP as a record, I just can't seem to understand that that would mean. I couldn't see how the same table could house product/supplier junctions records as well as a record with HACCP related to it.

    From the way you describe the one to many relationships above, I could say that about any of my tables. In regards to my tableSuppliers, any supplier has a contact person, their job title, phone, fax, etc. One supplier has many different parts of information, would you also consider this a one to many relationship? In my mind, it is not, all that information is related just to that supplier.

    Click image for larger version. 

Name:	AAAA.jpg 
Views:	7 
Size:	24.0 KB 
ID:	7027Click image for larger version. 

Name:	AAAAAA.JPG 
Views:	4 
Size:	44.8 KB 
ID:	7028

  14. #14
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    A record in a very basic way could be considered as equivalent to a row in Excel

    First the attributes that relate to a particular product/supplier combination are not in the product/supplier junction table but rather another junction table (see below for the full structure).

    If a product from a particular supplier is HACCP compliant you would add a record with that attribute to tblProductSupplierAttributes. If the product from a particular supplier is NOT compliant with HACCP you would not have a corresponding record with that attribute in tblProductSupplierAttributes. In other words, you only assign the attributes that are applicable to the product not ones that are not.


    tblProductSuppliers
    -pkProdSuppID primary key, autonumber
    -fkProductID foreign key to tblProducts (long integer number datatype--not text)
    -fkSupplierID foreign key to tblSuppliers (long integer number datatype--not text)
    -fkCountryID foreign key to tblCountry (long integer number datatype--not text)

    tblProducts
    -pkProductID primary key, autonumber
    -ProductNumber
    -ProductName

    tblSuppliers
    -pkSupplierID primary key, autonumber
    -txtSupplierName

    tblCountry
    -pkCountryID primary key,autonumber
    -txtCountryName


    tblAttributes
    -pkAttributeID primary key, autonumber
    -txtAttribute

    tblProductSupplierAttributes (this is another junction table)
    -pkProductSupplierAttributeID primary key, autonumber
    -fkProductSupplierID foreign key to tblProductSuppliers
    -fkAttributeID foreign key to tblAttributes

    tblChemicals
    -pkChemicalID primary key, autonumber
    -txtChemicalName
    -txtCASNo
    -fkChemCategoryID foreign key to tblChemicalCategory

    tblProductSupplierChemicals
    -pkProdSupChemID primary key, autonumber
    -fkProductSupplierID foreign key to tblProductSupplier
    -fkChemicalID foreign key to tblChemicals

    tblChemicalCategory
    -pkChemCategoryID
    -txtChemicalCategory

    From the way you describe the one to many relationships above, I could say that about any of my tables. In regards to my tableSuppliers, any supplier has a contact person, their job title, phone, fax, etc. One supplier has many different parts of information, would you also consider this a one to many relationship? In my mind, it is not, all that information is related just to that supplier.

    Funny that you should mention the supplier table. I was going to address that next.

    If a supplier only has one contact then you have a one-to-one relationship and that part of your table structure is correct, but what if you have more than one contact at a supplier? That would be a one-to-many relationship.

    Further, if a contact has many phone numbers (fax, mobile) or more general, has many ways of contacting them (phone, fax, mobile, e-mail addresses), that describes a one-to-many relationship. Also, since a particular contact method applies to many people you have another one-to-many relationship. Again, this forms a many-to-many relationship between contacts and contact methods, so another junction table

    I will assume that you might have more than one contact at a supplier. So this is the structure I would use

    tblSupplier
    -pkSupplierID primary key, autonumber
    -txtSupplierName
    -txtAddress
    -txtCity
    -txtState
    -txtZip

    tblSupplierContacts
    -pkSupplierContactID primary key, autonumber
    -fkSupplierID foreign key to tblSupplier
    -txtFName
    -txtLName
    -fkJobTitleID foreign key to tblJobTitles

    tblJobTitles
    -pkJobTitleID primary key autonumber
    -txtJobTitle

    tblContactMethods (would have the following records: phone, fax, mobile, e-mail)
    -pkContactMethodID primary key, autonumber
    -txtContactMethodName

    tblSupplierContactInfo
    -pkSupplierContactInfoID primary key, autonumber
    -fkSupplierContactID foreign key to tblSupplierContacts
    -fkContactMethodID foreign key to tblContactMethods
    -txtContactInfo (the actual phone # or mobile # or e-mail address goes in this field)

    I have seen some people recommend putting people's names and company names (a company is just a group of people) in the same table and relate them (contacts to the company) in another table like this

    tblPeople
    -pkPeopleID primary key, autonumber
    -txtPName (primary name-- the name of the company or the last name of a person)
    -txtSName (secondary name-- the first name of a person, may be left blank for a company)
    -txtAddress
    etc.


    tblRelatePeople
    -pkRelatePeopleID primary key, autonumber
    -fkPPeopleID foreign key to tblPeople (references the company)
    -fkSPeopleID foreign key to tblPeople (references thec contact at the company)

  15. #15
    carrod65 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    21
    JZ I can't thank you enough for the great amount of time you have put in helping me with this. When I get home tonight I am going to try and get all the tables you have recommended above into the database and and then try and pull it all together with the relationships. Once I feel this is working, then I will get cracking on the forms that will be used to display and input the information!

    Thanks for bearing with my limited knowledge of access and database design ( I have been trying to read/watch videos to learn, but there is so much information out there). If only there were more people out there with your patience and wisdom.

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

Similar Threads

  1. Table Relationships
    By ledbyrain in forum Access
    Replies: 1
    Last Post: 09-07-2010, 05:05 PM
  2. Plz help Table Relationships
    By heominhon127 in forum Database Design
    Replies: 6
    Last Post: 09-06-2010, 01:36 PM
  3. Table Relationships
    By goestejs in forum Database Design
    Replies: 3
    Last Post: 08-23-2010, 07:39 AM
  4. Table Relationships?
    By Meld51 in forum Access
    Replies: 3
    Last Post: 03-01-2010, 08:34 AM
  5. Table Relationships
    By jp2access in forum Database Design
    Replies: 3
    Last Post: 06-19-2009, 10:20 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