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

    Orders table with many kinds of Products.

    I've seen many examples of databases that include a Products table, in which many different types of products can coexist because their field structure is the same. My trouble is that I have a variety of products that absolutely cannot share the same table due to having dissimilar field names/types. When I show the relationships table below, my gut says it looks off. Now, I know I have to have an Orders table that holds generic information that all products share: a customer name, date ordered, etc. The great thing about having a standalone Orders table which all products share is the ease of adapting it to a Form with a datasheet where I can immediately see all my orders and how old they are and their completion status and so on. The SQL for that datasheet requires only 1 table - Orders. So I'm happy with that part.

    Click image for larger version. 

Name:	Relationships.jpg 
Views:	21 
Size:	122.4 KB 
ID:	29247



    Things get really confusing when I try to make a form for, lets say, just the shower bases. Dropping controls onto a form feels very awkward because you'd almost expect it to be a Form (Orders) with a Subform (Orders_Showerbases), but it's not. In fact it doesn't even feel like a true one-to-many relationship. A single Order is expected to be linked to a single Showerbase, or Vanity Top, or Wall System, etc.

    Visually dropping the controls on the form, I feel like I have to separate controls linked to the Orders table from the more specific Showerbases table below:

    Click image for larger version. 

Name:	dual-table form.jpg 
Views:	20 
Size:	154.9 KB 
ID:	29248

    Am I setting myself up for failure?

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    For what it's worth, I think you're headed for trouble. You may expect a single order to be "linked" to a single vanity top, but what if I order one for my kitchen and one for the bathroom? Or what if I'm a contractor and order several at once for various jobs? Then there is the notion of having a form for each product plus adding tables and forms for each new product, to say nothing of what to do with any discontinued product. For this,
    ...cannot share the same table due to having dissimilar field names/types
    I cannot imagine the tables as having been set up correctly. I've seen parts tables for everything from tiny fasteners to huge rolling mill components - all in one table. If your descriptions/product characteristics are so different that there would be too many null fields in a products table, there ought to be description tables that deal with groups of characteristics with the product key as an FK.
    Last edited by Micron; 06-26-2017 at 11:57 PM. Reason: fixed tags
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    MatthewGrace is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    159
    Quote Originally Posted by Micron View Post
    You may expect a single order to be "linked" to a single vanity top, but what if I order one for my kitchen and one for the bathroom? Or what if I'm a contractor and order several at once for various jobs?
    Great questions, let's pretend you order a vanity top for downstairs and one for upstairs. With the table layout above, you have to enter everything in the Orders table twice: date ordered, customer name, etc. That feels unnecessarily redundant. But it's necessary, because they may be arranged for production and shipping at two different dates, or both ship on different skids, or one be a rush and the other not, etc. The "administrative fields" (orders table) that deal with the logistics of the order can never encompass more than one product! Therefore, you'll never see "Quantity: 4" or anything. Try and understand that the fields Products are not merely static descriptions of a preexisting Product off a shelf, but require the user to fill in each field as you would fill in a custom order form with original measurements unique to you. Assume no one buys the same product with the same measurements twice (it happens, but rarely).

    As an example of why each product requires its own table, here are some examples of fields regarding the vanity top in your own bathroom at home:
    - Deck color
    - Bowl color (doesn't have to match deck)
    - Bowl style (there's over 70 bowls available)
    - Left/Right/Both sidesplash
    - Length (examples: 25", 37.5", 77.25", again, totally custom every time)
    - Edge Treatment Design (8 patterns available)
    - How many faucet holes?
    - ... tons more...

    Now lets contrast that the shower base:
    - Drain distance from backwall
    - How high is the threshold (the part you step over)
    - Should a ramp be used instead for wheelchair access?
    - How many tubes of adhesive are required to bond the water dam perimeter

    Notice how the fields required for each have nothing in common. To my mind, this necessitates separate tables for each type of product, and the interesting thing is that unlike most product tables I see, this one has to store unique information about each order. It's not just a description of a shower base. Rather, I have to enter in custom information each time.

    It's almost tempting to just eliminate the Orders table entirely and make a single table for Shower Bases (containing everything from date ordered, to status, to custom drain location, etc) and then a single table for Vanity Tops and then a single table for Shower Walls, etc, each with all their own idiosyncracies. They could all be custom order forms in and of themselves.

    It would a breeze, if it weren't for two things:
    1) How do I make a Query that gathers just the Administative fields (Customers, Product Types, Order Dates) for use in an easy to read dashboard of all these orderforms and
    2) Even if I could, I don't understand how resource demanding that could get to be. Each product table (ie. Vanity Top) could have 20 fields. 5 Different products like that could be a 100 fields. Perhaps I could make a query for each one that only grabs the top 10 most important admin fields, then Union query them all. Hm. That sounds like a ton of work for the engine even with only a few thousand records total.

    any ideas?
    Matt

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    , I don't understand how resource demanding that could get to be
    you would construct your tables for resources vertically, not horizontally

    tblProductTypes
    ProductTypePK
    TypeDesc

    tblProductTypeProfile
    ProfilePK
    ProductTypeFK
    OrderLineFK (this would be 0 to act as a template, (i.e. app would copy these to the same table, but populating with the appropriate OrderLineFK for the user to complete the value fields)
    ProfileDesc (e.g. bowl color, height of threshold)
    ProfileValue (null for template, to be completed by user)
    ProfileValueType (profilevalue field type would be text, this field tells the app if it need to be converted to a number etc.)
    Profile units (inches, feet, etc)
    ... may be some others

    tblProducts
    ProductPK
    ProductName
    ProductTypeFK
    ...other common product fields

    tblOrders
    OrderPK
    OrderDate
    CustomerFK
    ...other order fields

    tblOrderLines
    OrderLinePK
    OrderFK
    ProductTypeFK (doesn't need to be here, but enables cascading combos for a continuous form)
    ProductFK
    Quantity
    ... other common line fields

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Ajax's post wasn't here when I started this book of an answer, but I'm going to leave it as is.
    I believe it's pretty much universally accepted by seasoned Access developers that if you have to add fields (or worse tables) because you add another type of an entity, you have a poor design, so it's highly unlikely you're going to convince me otherwise.
    With the table layout above, you have to enter everything in the Orders table twice
    The standard practice is to have one order record per order (thus entering basic info once) and a related order items table that comprises the many side. If you cannot because of your design, I'd blame that design.

    I have not treated this subject as I would any other design where I take the usual approach, so this is off the cuff.
    I see tblProducts having Type / SubType fields (top/vanity; top/kitchen; top/bar; top/bathroom; base/shower...). A new product only requires a new line. I don't see why ALL the top charactreristics could not go into one table if you want to separate top data from base data. This gets related to tblOrderItem line ID. Things like bowl style go into a lookup table. Again, I would attempt to avoid having to add a table for each commodity that requires the lookup feature by having Type/SubType fields. In any of these cases, these 2 fields would be a composite index, thus allowing bowl/ceramic and bowl/steel but each combination only once.

    require the user to fill in each field as you would fill in a custom order form with original measurements unique to you.
    This is different from any other db app how? During the order entry process, your user should be face with fields that are set as required as in any normal data entry process.

    IMHO, you need to step back and (re-?)review normalization and design or the trouble you could be headed into is worse than what you envision now. Having a table per commodity, then a plethora of queries just tells me it is becoming overly difficult. If these links don't help, try looking for others. Then maybe post back with your intended design. There are also MANY examples out there for order databases that should give you ideas.

    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.ca/...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/

    How about it, my esteemed peers? Am I off base here?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    Others here have pointed you in right direction, but maybe some systematic overview is handy.

    Practically you have 2 connected information lines.
    1. line: Product/Part/Article.
    My advice is, define every product which signinically differs from others as separate product. I.e. when you have red bowl and blue bowl, and yo must deliver the bowl of specific colour, then you need 2 separate product codes for them. When you need for some reason also refer p.e. to bowls in general, then you have to define for every product a product group in Products table.
    Now, as I understood, you want for every product it's properties also. Then you add another table, where you enter all those proprerties - a row for every property of product. There you have additionally to ProductPropertyID (can be autonumeric) productID, PropertyDescription and PropertyValue (last 2 fields can be text fields, as info there is merely for information). P.e.
    1; 1; Colour; Blue
    2; 1; Style; Simple
    3; 1; Length; 25"
    etc.

    2. line: Orders.
    My advice here is - keep products out of orders Orders table is about client/customer and financial info. Order Date, Payment Termin, Client/Customer info, Sum, VAT, etc. Whenever you have several of those, you must have several orders.
    Products belong to Order Rows table, where you have along with ProductRowID ProductID, Quantity and single product Price. Principially is it possible to have for every row here different delivery conditions, but it is a reciepe for disaster almost for sure, and a pain in somewhere for you personally.

    It must be enough for start.

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    My $0.02.........

    Were I you, I would listen to Micron and Ajax. FWIW, I totally agree with them.
    It seems like you have jumped into how to enter data, rather than how to store the data and the relationships.

    To me, the relationship window image in Post #1 resembles a spreadsheet design rather than a RDBS design...

    Good luck with your project.......

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

Similar Threads

  1. problem with an orders send to products
    By Mehvan in forum Access
    Replies: 2
    Last Post: 01-12-2017, 09:24 AM
  2. Replies: 2
    Last Post: 11-01-2016, 09:04 AM
  3. Replies: 5
    Last Post: 07-10-2014, 09:37 AM
  4. Replies: 8
    Last Post: 04-24-2012, 01:05 PM
  5. Orders & Products
    By mastromb in forum Database Design
    Replies: 4
    Last Post: 01-22-2010, 07:59 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