Results 1 to 2 of 2
  1. #1
    thevaik is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2013
    Posts
    8

    Design help - Order Details table that can store items from multiple tables

    I have a standard Orders table with Order Details for each line item. My issue is that the Order Details line can come from one of several tables.

    For example the Order Details line which stores the primary key of the item being sold could be from the Inventory table, the Custom table, the Rental table, etc.

    Seeing as each of these item tables have the same autonumber primary keys, I need to figure out a way to mach the items being sold to the Order.



    My current train of thought is to create a different Orders Details table for each table that includes items that can be sold. Would this design be sufficient? Or is there a better way to go about it?

    Unfortunately it is too late to assign unique primary keys to each tables (i.e. appending a special character to the primary key to indicate which table it comes from).

    I am also having trouble trying to figure out how query the Order Details tables so that they can be viewed on the Order form.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    My first thought would be NOT to create multiple OrderDetails tables. You should investigate what factors/features make these Custom/Rental/Inventory tables separate. Perhaps you could generalize Items and have a ItemType field to differentiate the various Item (sources).
    This link may help http://www.liuc.it/person/gbuonann/I...es/05_ER_6.pdf

    I have posted links to videos that may be useful at Post #11 at
    https://www.accessforums.net/program...ues-30333.html

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

Similar Threads

  1. Replies: 8
    Last Post: 11-12-2012, 05:20 PM
  2. Replies: 2
    Last Post: 06-18-2012, 03:33 PM
  3. Design tables in order to keep history
    By snoopy2003 in forum Database Design
    Replies: 2
    Last Post: 02-22-2011, 02:43 PM
  4. a simple purchase order details
    By damie in forum Database Design
    Replies: 16
    Last Post: 12-30-2010, 04:14 AM
  5. table design: one big table vs. multiple small tables
    By lstairs in forum Database Design
    Replies: 3
    Last Post: 12-31-2009, 08:46 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