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.

2
  • I sure hope you're not expecting the customer's name to be unique... 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

1 Answer 1

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.

1
  • 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. Oct 3, 2012 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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