I have a table called Accounts which has a primary key called AccountID which is an integer. I would like to add some tables to the database that will have AccountID as a foreign key. One of these tables is a list of transactions per account. The Transactions table will have the following two columns:
* TransactionID int NOT NULL
* AccountID int NOT NULL (foreign key)
I am having trouble deciding on how to define the primary key for this table. Should it be:
Option 1: ========= CONSTRAINT (PK_Transactions) PRIMARY KEY (TransactionID) I.e. each row will have a unique TransactionID. Option 2: ========= CONSTRAINT (PK_Transactions) PRIMARY KEY (AccountID, TransactionID) I.e. For each AccountID, there will be a unique transactionID. E.g. If AccountID 99 has 2 transactions, the Transactions table will look like this AccountID TransactionID --------- ------------- 99 1 99 2
Which option do you recommend? Are there any other options I should be considering? Thanks!
I would recommend a third option (which might actually be your first option).
CREATE TABLE t_transactions ( TransactionID INT IDENTITY , AccountID INT NOT NULL CONSTRAINT XFK01t_transactions FOREIGN KEY (AccountID) REFERENCES t_accounts (AccountID) -- other columns as needed )
This gives you a clean PK (Primary Key), so that if you need to switch a transaction from one account to another, you don't have to worry about renumbering anything. It also preserves the relationship between the transaction and the corresponding account. I think that this gives you the best of both worlds.
For More Information
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Database Design Web Links: tips, tutorials, scripts, and more.
- Have a Database Design tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Database Design questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our Database Design guru is waiting to answer your toughest questions.
Dig Deeper on Oracle database design and architecture
Related Q&A from Pat Phelan
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.