I was discussing about database layout/design with a friend and I got some questions about performance and best practices. I'm mainly a software programmer, so I may lack "common sense" regarding databases, so if you can please explain as you would for a newbie.
I have a table that records the transactions of users and their credit card usages. It has a few millions of records per day. People in my country have a 11 digit number that is supposedly unique.
I've always designed tables with an auto-increment int column as identity. In this case, the identity column is a composite of the user's social number + the date, which is a huge number column, and the fact that it's non-incremental seems like it's worse when doing a query with WHERE. What's the best option in this case?
Another question is about primary keys, stores do need to look up the table and they usually send the social number or, most commonly, the user's name. Would making the user name column as primary key enhance performance? And why is that?