Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2008
    Posts
    11

    Multiple Child Tables

    Suppose we have a business database containing (among others) an "Orders" and an "Invoices" table. We want to be able to record notes/activities about any order or invoice --- by date, author, and activity comments (essentially an activity log), so we create an Activities table containing ActivityID (PK),ActivityDate, Author, and Comments.

    In order to use the Activities table for both Orders and Invoices, we create create 2 additional tables (OrderActivities --- consisting of columns OrderID and ActivityID and InvoiceActivities --- consisting of columns InvoiceID and ActivityID)

    However --- the relationship would be one->many from Orders->OrderActivities but only one->one from Activities->OrderActivities.

    This is just an example -- in reality, I have appr 10 tables that will need "child" activity tables.

    Can anyone suggest a more efficient database structure?

    Thanks!

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    What do you mean by "more efficient"? I guess your OrderActivities table looks something like this:

    CREATE TABLE OrderActivities
    (ActivityID INT NOT NULL PRIMARY KEY REFERENCES Activities (ActivityID),
    OrderID INT NOT NULL REFERENCES Orders (OrderID));

  3. #3
    Join Date
    Oct 2008
    Posts
    11
    The PK of OrderActivities would consist of 2 FKs --- OrderID (from Orders) and ActivityID (from Activities). It is basically an intersection table between Orders and Activities.

    I keep thinking that something is "wrong" relationally with this table structure because I normally think of intersection tables as resolving a many-many relationship. In this case, there is a one-many relationship between Orders-OrderActivities but a one-one relationship between Activities and OrderActivities.

    Is this a "sound" database structure? I may need to add additional columns to the Activities table, so really would like to keep this as a "generic" table, as opposed to having 10+ virtually identical "activities tables (i.e., all containing Author, Comments, Date, etc)

    I was just wondering if anyone could suggeat a cleaner database structure for this situation.

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by BARJRD
    The PK of OrderActivities would consist of 2 FKs --- OrderID (from Orders) and ActivityID (from Activities). It is basically an intersection table between Orders and Activities.
    The reason why I made ActivityID the key of OrderActivities was to make a one-to-one relationship (optional 1-1 that is) between Activities and OrderActivities.

    What you are describing on the other hand is a one-to-many between those tables. I don't understand why you would want to do that because it is wrong according to your description.

  5. #5
    Join Date
    Oct 2008
    Posts
    11
    Sorry---yes, you're absolutely right (ActivityID IS the PK of OrderActivities) --- I'm brain-dead at the moment. Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •