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...)
or
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.
Best 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.