Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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. –  vasile Oct 3 '12 at 6:11

1 Answer 1

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.

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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