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!

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));

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.

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.

Sorry---yes, you're absolutely right (ActivityID IS the PK of OrderActivities) --- I'm brain-dead at the moment. Thanks