Results 1 to 10 of 10
  1. #1
    MatthewGrace is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    159

    Does this table relationship make sense?

    I see a lot of tables that look like: Customers->Orders->Products

    But in my case, I can't seem to lay it out that same way, and it makes me think I'm doing something wrong. In short, a "Products" table will not work because the products offered have no features in common, yet still need to be linked to an Orders table. As an example, look at the attached pic showing a Relationship: "tblFlooring" and "tblCountertops" have fields unique to them, yet share certain administrative information shown in the orders table like the Date it was placed.

    Click image for larger version. 

Name:	Forked relationship.jpg 
Views:	25 
Size:	46.5 KB 
ID:	18305

    Maybe this is completely normal, but I can't tell. I always see examples in books and on tutorials of strictly linking each table to the next, one after the other, like a chain. I came up with this way on my own and it feels alien to what I've learned so far.



    More still, "tblFlooring" and "tblCountertops" are just the beginning, I could throw in 6 or 7 other products each needing specific tables of their own.

    Thank you for any input,
    Matt

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Customers->Orders->Products
    You are missing a table.

    Customers->Orders->Order_details <- Products.

    Database Answers web site has some models
    Here is Customers and Orders example: http://www.databaseanswers.com/data_...ders/index.htm
    Here is Customers and Products example: http://www.databaseanswers.com/data_...ucts/index.htm

    More still, "tblFlooring" and "tblCountertops" are just the beginning, I could throw in 6 or 7 other products each needing specific tables of their own.
    If each product has its own table, you wouldn't have a normalized table structure.

    What are examples of different products and Specifics?

    Flooring - Specific1
    Flooring - Specific2
    Flooring - Specific3
    Countertops - Specific1
    Countertops - Specific2
    Countertops - Specific3
    .
    .
    .

  3. #3
    MatthewGrace is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    159
    Thank you for your reply. I will look into the links you provided.

    You asked for some examples of what the different products might be:
    Countertop related fields examples: Does it have a sink? How many inches from the wall is the drain on the sink to be attached? How thick is the deck (1" vs 1 1/2")? Does it have a backsplash? Sidesplashes (Right, left, both)? etc..

    Floors... let's pretend there are shower floors, have special fields too: Should they be slip resistant? Handicap accessible ramp built-in, How many inches from the backwall and left wall is the drain to be placed, Does it have flanges for water protection around the perimeter, etc.

    Notice how none of these fields are related or could be shared.

    In the example I gave in the pic above, I really thought it was normalized since, there is no redundancy in the code.

    I will study what you gave me. Thanks,
    Matt

  4. #4
    MatthewGrace is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    159
    After reviewing the links, I still have the same opinion... I don't understand how unrelated items (which seem to demand absolutely unrelated fields as in the examples I listed above: countertops vs. flooring) could share a table like "Order Details". The only way I can see no redundancy in the table is to outline it as in the pic in my first post.

    Maybe this model (in the pic) is just a rare implementation of the correct way to do it for certain needs?

    Thanks,
    Matt

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Products would normally be in one table.

    tblProducts
    ProdID ProdCat ProdDesc Unit SuppID
    1 Flooring Laminate sq ft 3
    2 Flooring Tile sq ft 3
    3 Flooring Carpet sq yd 1
    4 Counters Granite sq ft 2
    5 Counters Quartz sq ft 3
    6 Cabinets Oak ea 2

    Then OrderDetails would have records that associate products with order.
    ID OrderID ProdID Quantity
    1 123 2 500
    2 123 6 2
    3 456 1 200
    4 456 5 25
    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
    MatthewGrace is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    159
    Examples of Fields in Countertops:
    - Bowl type (since countertops have a bowl with a sinks)
    - Bowl color
    - Sidesplash (left/right/both)
    - Deck thickness (1" or 1 1/2")

    Examples of Fields in Showerfloors:
    - Drain locations (it could be anywhere.... 15" from the left wall and 18" from the backwall)
    - Threshold/Ramp for handicap access (it's dimensions, it's colors)
    - Certain types of water barrier gear used during installation for this kind of product.

    Notice how the fields bear no similarities. You can't put a handicap accessible ramp with all its dimensions and features in the same table as a countertop... which has it's own unique fields too. Right?

    Because of that reason, I cannot help but think that the products then require different tables. here is a new pic, further refined in an effort to reduce confusion:

    Click image for larger version. 

Name:	Untitled.png 
Views:	18 
Size:	20.9 KB 
ID:	18311

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You aren't talking about product listing, you are talking about job specifications. Not quite the same thing. A slab of granite is always what - 4'x6' - how you cut it for each job will vary. A double bowl 33x22 sink will always have that description but where it is placed in a counter will vary. However, sounds like you don't sell granite or sinks, but sell a service and the granite and sink are elements of that service - which is to build something. Your product is a job. Whether or not to break down the job into multiple items (a whole-house reno with kitchen, bathroom, etc each as a separate line item on the order) needs to be determined. Instead of OrderDetails as related table, I am thinking JobSpecifications would be the detail table.

    I got a bid for home renovation and it was presented as a nice, multi-page report grouped by room with job specs/costs under each room and thumbnail CAD sketch for each room layout. Impressive.

    You might want to look at some software for construction job bidding/costing.
    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.

  8. #8
    MatthewGrace is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    159
    Hm. I don't see a way around putting job specifications in the products form.

    Trouble is I don't sell something simple like bottles of olive oil that come in fixed sizes with fixed prices. Rather, every single countertop is unique.

    I will have to come up with something.

    Last question: Even though the way I've proposed it is weird, is there anything explicitly wrong with it? MS Access did not throw up a red flag saying "you can't have a forked table relationship like this...". To test it in a real "Order Form", I created a blank form and dropped all the fields from my Orders table and Countertops table onto it. All the linking is automatic, it works no problem. Same thing with the Flooring table.

    What you get looking at your table data is no different than your typical one-to-many relationship, but the foreign keys look a little weird like this:

    ORDERS TABLE
    OrderID Field
    1 ...
    2 ...
    3 ...
    4 ...


    COUNTERTOP TABLE
    CountertopID OrderFK
    ... 2
    ... 3

    FLOORING TABLE
    FlooringID OrderFK
    ... 1
    ... 4

    The foreign keys skip around a bit on the "many" table but other than that it seems completely normal. Is there a specific rule I'm breaking or other database wrongdoing happening here?

    Thank you,
    Matt

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can certainly save the OrderID as FK in the detail tables but I think you will find data entry difficult. Also, report design will likely require subreport for each detail table.

    I did a few edits on my previous post you may have missed. Might review again.
    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.

  10. #10
    MatthewGrace is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    159
    Thank you again, June. I will likely proceed with my current table layout and do my best to manage its shortcomings. Solved.

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

Similar Threads

  1. Can I make an Outer Join a one way relationship?
    By johnjmcnaughton in forum Programming
    Replies: 19
    Last Post: 05-01-2013, 09:48 AM
  2. Replies: 1
    Last Post: 04-26-2013, 05:30 PM
  3. Replies: 2
    Last Post: 04-08-2012, 03:04 PM
  4. Replies: 2
    Last Post: 10-27-2009, 07:09 AM
  5. Replies: 1
    Last Post: 10-28-2008, 08:38 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