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?
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.