What is the best relationship model for high performance?

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

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.

I sure hope you're not expecting the customer's name to be unique... — Clockwork-Muse, Oct 2, 2012 at 23:03
It's not a person name, but a unique system-allocated identifier, because my customers are machines. — Sam, Oct 3, 2012 at 6:11

Colin 't Hart · Accepted Answer · 2012-10-02 10:27:21Z

2

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.

answered Oct 2, 2012 at 10:27

Colin 't Hart

9,25215 gold badges35 silver badges43 bronze badges

I seriously doubt that the size of the id is going to make that big of a difference. I'd probably go with a fk regardless (int or the actual id), just in case things have the possibility to change.
– Clockwork-Muse
Oct 3, 2012 at 15:36

Add a comment |

1 Answer 1