Q
Problem solve Get help with specific problems with your technologies, process and projects.

Adding tables that will have AccountID as a foreign key

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


Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close