Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Dbar
Starting Member
12 Posts |
 Posted - 2008-12-21 : 09:16:50
|
| 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! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2008-12-21 : 09:42:18
|
| Unless I'm missing something just make the ActivityID column in OrderActivities unique, or make it the primary key. |
 |
|
|
Dbar
Starting Member
12 Posts |
Posted - 2008-12-21 : 11:19:05
|
| I was thinking that the PK of OrderActivities would consist of OrderID+ActivityID. I always think of intersection tables as resolving a many-many relationship and wonder if the one-one relationship between the Orders table and the OrderActivities is OK? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2008-12-21 : 11:38:21
|
| You didn't say there'd be one-to-one for Orders->OrderActivities, in the original post you said there's be one-to-many.If you need one-to-one for Activities->OrderActivities, you can make ActivityID unique, and still have OrderID,ActivityID as primary key. It's redundant though if you have one column already unique, might as well make it primary key. And if you cluster on primary key (the default) having the smaller key will save space in your indexes on that table. |
|
|
|
Dbar
Starting Member
12 Posts |
Posted - 2008-12-21 : 12:55:45
|
| You're right, the PK of OrderActivities would be ActivityID, since it is in a one-one relationship with Activities. Thanks. |
|
|
|
|
|
|
|
|
Multiple Child Tables?