CODEWITHSUNDEEP
mysqlindexing
João Guilherme
João Guilherme

Reputation: 513

Mysql - Should I use ID columns?

I have a doubt about best practices and how the database engine works.

Suppose I create a table called Employee, with the following columns:

The thing is.. I see a lot of databases that all its tables has and aditional column called ID, wich is a sequencial number. Should I put and ID field in my table here? I mean, it already has a Primary Key to be indexed. Will the database works faster with a sequencial ID field? I dont see how it helps if I wont use it to link or research any table.

Does it helps? If so, why, what happens in the database?

thanks!

EDIT ----- This is just a silly example. Forget about the SS_ID, I know there are better ways for choosing a primary key. The main topi is because some people I know just ask me to add the collumn named ID, even if I know we wont use it for any SQL query. They just think it helps the database's performance in some way, specially because some database tools like Microsoft Access always asks us if we want it to add this new column.

This is wrong, right?

Upvotes: 1

Views: 920

Answers (4)

Romain
Romain

Reputation: 12819

The actual performance gain in having a sequential id is going to depend a lot on how you use the table.

  • If you're using some ORM framework, these generally work better having a sequential ID of an integral type [1], which is typically achieved with an sequential id column.
  • If you don't use an ORM framework, having an idkey that you never use and a surrogate ss_id key which is effectively what you always use makes little sense.
  • If you're referencing employees from other database table (foreign-key), then it'll probably be more efficient to have an id column, as storing that integer is going to consume less space in the child tables than storing the ss_id (which I assume is a CHAR or VARCHAR) everywhere.

On the ss_id, assuming it's a social security number (looks like it would be), there might be legal & privacy concerns attached to it that you should care about - my answer assumes you do have valid reasons to have social security numbers in your database, and that you would be legally allowed to use & store them.

[1] This is usually explained by the fact the ORM frameworks rely on having highly specialized cache mechanisms, that are tailored for typical ORM use - which usually implies having a sequential id primary key, and letting application deal with actual business identity. This is in fact related to consideration very similar to these of the "foreign key" considerations.

Upvotes: 4

Cylindric
Cylindric

Reputation: 5894

US Social Security numbers are not sufficiently identifying. And banks certainly do not use them in that way. Not everybody has one. Errors result in duplicates. Foreigners don't have them. They are far too fragile to use as a database PK.

Most importantly: the are resused after death

Do some research: SSN as Primary Key

Upvotes: 4

jray
jray

Reputation: 1226

What's more important (obviously) is that you have a primary key, as long as the data you put use for that primary key will be uniquely identifiable. In your example, SSN's are uniquely identifiable which is why banks use them and will work. The problem with this example is that your Employee ID is likely to be used as a Foreign Key in other tables, which means you're taking personal information (that is legally protected) and spraying it across your data model. You might do better using an Auto Incremented field in this case.

Upvotes: 3

duffymo
duffymo

Reputation: 308763

If SS means "Social Security", I'd strongly advise against using that as a PK. An auto-incremented identity is the way to go.

Using keys with business logic built in is a bad idea. Lots of people are sensitive about giving SS information. Your app could be eliminating part of their audience if they use SS as primary key. Laws like HIPPA can make it impossible for you to use.

Upvotes: 6

Related Questions