1

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 '12 at 23:03
  • It's not a person name, but a unique system-allocated identifier, because my customers are machines. – Sam Oct 3 '12 at 6:11
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.

  • 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 '12 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.