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 |
|
studentbelfort
Starting Member
12 Posts |
 Posted - 2008-02-01 : 10:49:49
|
| HelloI have this 3 tables, Customer, Transaction and Book. Since a customer can buy the same book more than one time, I've defined Transaction_Nr as primary key of Transaction and put Customer_Nr and Book_Nr as foreign keys. So the layout is more or less like this :Customer {Customer_Nr, Customer_Name}Transaction {Transaction_Nr, Date, Book_Nr, Customer_Nr}Book {Book_Nr, Book_Name}So now my question is, would it have been better, to define Transaction_Nr, Book_Nr and Customer_Nr as a combined primary key or to leave Transaction_Nr alone ? What would be the difference ?Thanks ! |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-02-01 : 10:54:02
|
| i guess this really depends on whether Transaction_Nr is unique? if a customer buys more than 1 book at the same time, is that the same transaction?Em |
 |
|
|
studentbelfort
Starting Member
12 Posts |
Posted - 2008-02-01 : 11:01:16
|
| Hi Elancaster, this is a good question, I haven't thought about this....but I think it would be then more or less the same, wouldn't it ? Considering that Transaction_Nr is unique and Costumer 1 buys Books 2,3 and 4, we would have :Transaction_Nr Customer_Nr Book_Nr 1 1 2 2 1 3 3 1 4If all of them were the primary key :Transaction_Nr Customer_Nr Book_Nr 1 1 2 1 1 3 1 1 4 3 1 4I guess it would be pretty much the same, wouldn't it ? |
|
|
|
studentbelfort
Starting Member
12 Posts |
Posted - 2008-02-01 : 11:02:21
|
| Sorry, no 314 at the end..... |
|
|
|
studentbelfort
Starting Member
12 Posts |
Posted - 2008-02-01 : 12:32:50
|
| Bump. Anyone ? |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-02-01 : 12:42:39
|
| This seems like a pretty standard Order-Detail scenario. You might want to do some searching on the topic. But, it appears that you would have one table of Transactions (Order) and a OrderDetail or LineItem table to capture the individual items that make up the order. Something like:Transaction (TransactionID (PK), CustomerID, Date, ...)TransactionLineItem (TransactionID(PK), LineItemNumber(PK), BookID, Quantity, ... ) |
|
|
|
studentbelfort
Starting Member
12 Posts |
Posted - 2008-02-01 : 13:03:21
|
quote:
Originally posted by Lamprey
This seems like a pretty standard Order-Detail scenario. You might want to do some searching on the topic. But, it appears that you would have one table of Transactions (Order) and a OrderDetail or LineItem table to capture the individual items that make up the order. Something like:Transaction (TransactionID (PK), CustomerID, Date, ...)TransactionLineItem (TransactionID(PK), LineItemNumber(PK), BookID, Quantity, ... )
Yeah ok I'll do some searching on it. Today morning I wrote a test and this question was present. And as we in the class didn't look at it in this way you've just said, I suppose it was not required to be solved like this. I've just put the 3 of them TransactionId, CustomerId and BookId as primary key. Though I don't know what would make more sense, to put only TransactionId or the 3 of them. Any ideas ?Thanks |
|
|
|
|
|
|
|
|
Defining primary key