What is the best relationship model for high performance?

Posted on

Question :

I want to model a relationship between Customer and Transaction.

However, because Customer is only described by a unique string and no extra info, I have two possibilities to implement it in the database:

Customer(Id, NameString) --> Transaction(CustomerId, OtherDetails...)


Transaction(CustomerNameAsString, OtherDetails...)

I have to manage a few hundreds transactions per sec, and store a several million transaction in the table.

Each new transaction INSERT is done after 1 or 2 SELECT queries on the table, like SELECT count transactions (customer, lastMonth).

Is there a big performance penalty on one model or another?

In other words, what is the faster way? Should I use a join and an integer index, or a string index with no join?

I am using Oracle.

Answer :

You’ll probably want to keep your transaction table as small as possible, so use an integer column as a foreign key to your customer table.

Leave a Reply

Your email address will not be published. Required fields are marked *