Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73

    Table Relationships

    Hello again,



    I will explain as best I can but trying to put together the design of the parts section of my DB and working out what relationships are required. I've simplified it down so that a part can originate from 1 of 2 places, it will either be produced in house at our factory or bought in from an external supplier. Also a part can be bought in from more than 1 supplier depending on price/stock levels. I know this is where the many to many relationships come into play and so far I have done the following:-

    Relationship_Parts.pdf

    The parts are of course stored in tbl_Parts, a part can have an origin of either in house (made by us) or bought in (externally sourced) so I have added a link table with 2 PK's called tbl_SupplierToPart which links it to the suppliers table, tbl_Suppliers. You will also see another table, tbl_Origin, which I have linked to tbl_Parts but shouldn't this also be a link table between tbl_Parts and tbl_Part_Origin? The reason I ask is because one part doesn't come from just one supplier and a supplier will supply many parts, so is this not a many to many relationship??

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I get invalid attachment when I click on your link???

    Before creating Access tables etc, get a clear narrative of what your business processes and rules are in simple, plain English.
    There is an old saying in database--"if you can't describe it, then no one can build it".

  3. #3
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73
    I've changed the attachment to a pdf so it should hopefully work now

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    OK the attachment is readable.
    A few comments -- no particular order -
    -I would not use embedded spaces or special characters (#) in table field names
    -I don't think the link between dealer_contact and branch is needed, since you have the junction table ContactToBranch
    -I'm not sure tblPart should include ProductID??(don't think so but don't know your rules)

    It is difficult to provide any more detail since we do not have a list of requirements nor a business narrative to review the model.

    In most models/databases with Prices, it is often better to record a Price and quantity in any transaction and NOT refer back to a Parts table to get Price. The reason is that Prices can and do change, and if you have older transactions that refer to the Part table Price, all the historic values get changed. There are ways to resolve this, but the simplest is to use 2 prices (sort of) AgreedToPrice that is effective at the time of transaction(sale/purchase), and the current Part price recorded in the Part table. This isn't necessarily a factor in your set up; it is a comment about Price generally.

  5. #5
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73
    In response to your comments, which I appreciate very much and would welcome any others you may have.

    There are a few areas that I need to tidy up where I've used such characters!
    I have recently added the ContactToBranch table as I found out that in some instances, albeit very few, one contact serves more than one branch so the other link is yet to be removed.
    As for ProductID in the tblPart, my thought process was this might be needed to identify which part is used on what product/s?? I could be wrong and am not fully understanding here.

    I admit I've just gone and dived straight in to building the DB rather than drafting up a list of requirements, so I will do as you suggest and go back to reviewing what it is that I need it all to do.

    On the price issue, I see the error in what I have done now that you have pointed it out. Are you saying I need to create another link table for the AgreedToPrice so the historical values are stored here instead or should this just be another field in the Part table?

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Again these comments are made without considering your requirements which we have not seen.

    As for ProductID in the tblPart, my thought process was this might be needed to identify which part is used on what product/s?? I could be wrong and am not fully understanding here.
    I think that is handled in tblPartToProduct.

    My comment on Price is along this line --and it may not be relevant to your business-

    If you have a system with Customers who Order Products, suppose a Customer bought 10 ProdX with a unit price of $2.50 on Mar 3, 2009. And further suppose that the Price of $2.50 was stored in the Parts table. So on the Order there was a quantity of 10 and a Total Price of 25.00. As often/always happens in business, the Price of Part ProdX is changed to$ 3.00 in Dec 2009.
    If you reviewed your older Orders, and reprinted same, you will now find that your Total amount on that Order will be $30.00(not the $25.00 actually paid). There is a loss of historic data which is often an issue.

    Reviewing the process in more detail there are 2 prices involved in this business. First, is the Price of ProdX at the time of the Order; and second is the current Price of ProdX. It seems simple, but if you see that the "current price" changes with time, but the Order date does not, it becomes apparent that this different Price thing has to be accounted for. Easiest is to include Quantity and AgreedToPrice of Prodx on the Order. Also, AgreedToPrice can accommodate things like --loyalty program, clearance sale, specials etc.

    ..dived straight in to building the DB rather than drafting up a list of requirements
    You are not the first to do this. However, you can not test, nor validate, the model without having some specified requirements.

    You should work through this tutorial (30-45 minutes or less for you since you have a handle on modeling). It comes with a narrative, a problem definition and a solution. Pay particular attention to the process (or even the Hernandez approach overview). It can be used with any database.
    I also recommend you create some test data and work some test scenarios against your model.
    Here is a reference to "stump the model" that may be of use.

    You may also find these free videos helpful and a good reference.

    Database intro
    The Relational Model
    Data Modeling and the Entity Relationship Model



    Good luck.
    Last edited by orange; 05-26-2016 at 07:22 AM. Reason: formattiing

  7. #7
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73
    Hi there,

    thank you for all the information given above, I have worked my way through the tutorial which as you said would be straight forward enough though it did uncover a potential modification to the way I had things before.

    It got me thinking about the products we produce as each product needs to be listed under a specific category and there are several different models (e.g weight carrying capacities) for each product. Am I best keeping all the products in one table or separate them with a table for category, a table for product and then a final table for model, or would this be breaking it down too much? The main problem I have is that not every range has the same weight capacities, for example TrailerX has a model range of 10, 11, 12, 14, 16, 18, 20 and 24 whereas TrailerY has a model range of 8, 10, 12, 15, 20, 25 and 30. TrailerX is in a different category to TrailerY but after typing this I am starting to see it best to keep all the models/capacities in one table and apply a filter depending on what range is selected?

    Hopefully it makes sense what I am trying to describe above, I have a pretty good idea of how (or how I should) go about it and hoped you might have a little input from a more experienced point of view

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    The answer here is ---it depends. It really depends on your business and circumstances.
    If you were a car salesman or car manufacturer, you may not want all cars in 1 table, but there may by conditions where you would.

    Car --- foreign/domestic
    Car --- manufacturer
    Car --- model
    Car --- engine type (gas/diesel/electric)
    Car --- number of doors
    Car --- transmission type
    ....etc.

    In the car/your business what table structure is important...


    The are posts from time to time where someone's companies makes clothing. Consider this --we manufacture clothes (specifically sweaters and shirts) for men, women and children. The materials are cotton, polyester, rayon and wool and various combinations of materials in various percentages. Our shirt sizes are age 6 month, 1yr , 18 months and 24 months, then 4,5....14 years for children. We manufacture these in a variety of colours. What table structure should I use?


    What is important to you? How do you use the data in your processes?

    Sometimes, you can build a small prototype using a specific structure. Then test/exercise the prototype with some realistic scenarios. You may want to test 2 or 3 structures to see which is most applicable to your situation.

    By prototype here, I mean table structures and relationships (if applicable) to mock up some data. The data can be a few (10-20 records in each table). Pose typical business queries against the tables.
    Does it answer all queries? Are there anomalies (you can't get what you need...)? Does one prototype work better than others?

    A little testing (getting your feet wet) is better than a 1000 opinions from others.

    I would probably opt for multiple tables, but you know your business and I don't. I'm attaching a jpg to show many attributes associated with a finished product. For illustration only.




    Good luck.
    Attached Thumbnails Attached Thumbnails ConceptualFinishedProductWithManyAttributes.jpg  

  9. #9
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Orange,

    Thank you for the quick example. For some reason it made more sense to me than a lot of the other examples I have been reading. I have been reading all the links you have been posting. Your example made something click for designing my database.


    Again Thank you very much for all your time help to me and others.
    Walker

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You're welcome, good luck with your project.

  11. #11
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73
    After experimenting with your suggestion above to build up a specification into one table, I have had an error message pop up informing me that there are too many indexes in this table. Now I may need to normalize my data a bit more in my tables but I'm not so sure this will work as there are many different options. Do I need to split this information between two tables, or is this not such a good idea?

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Review Normalization. Do a tutorial or 2 for practice. Tables should be focused on a specific concept or thing- eg. Vendor, Product, Order, Employee.... Access allows up to 32 indexes per table. However, it is NOT common to have a normalized structure with 32 fields in a table. Relational data base tables are typically narrow and long.

    There are 9 tables in the sample jpg I posted.

    Perhaps you could show us a jpg of your current relationships.

  13. #13
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73
    This is what my data model looks like at present. You will see I have one main table with lots of foreign keys in it which I will populate using a build form but this has been halted by the aforementioned error message so I think this bit I may have done incorrectly.

    Relationship_Parts.pdf

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You have done an impressive amount of work - more attention to the model than most!!
    Are each of the "items" (axleId, CylinderId, BodyHeight, Chute...) set up with unique serial numbers?
    Do you have some sort of Model grouping in which all products of that Model have a number of features/attributes in common?

    Do you have a write-up about your business that puts the model into context?

    Can you post a copy of the database as you have it at the moment? Zip format.

  15. #15
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73
    Well they have their own unique identifier created by Auto Number yes and I believe I have attempted to group the models up, firstly by category, then a sub category of product type and then further still the model types (capacities).

    Here is the db zip....

    RWL_16DB.zip

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

Similar Threads

  1. Table Relationships
    By edthened in forum Access
    Replies: 12
    Last Post: 06-24-2012, 05:52 PM
  2. Table Relationships
    By carrod65 in forum Database Design
    Replies: 15
    Last Post: 04-04-2012, 09:22 AM
  3. Table Relationships
    By ledbyrain in forum Access
    Replies: 1
    Last Post: 09-07-2010, 05:05 PM
  4. Table Relationships
    By seanp in forum Access
    Replies: 2
    Last Post: 04-15-2010, 07:12 AM
  5. Table Relationships?
    By Meld51 in forum Access
    Replies: 3
    Last Post: 03-01-2010, 08:34 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