3

I've been reading through questions explaining why primary ID's should always be used in tables, especially if a record in one table needs to associate with a record in another table. However in my case the table is one of users, so has a name field. Surely for associating I could just use the name of the users to associate with, or am I missing something.

Cœur
  • 37,241
  • 25
  • 195
  • 267
w4etwetewtwet
  • 1,330
  • 2
  • 10
  • 20
  • whats happens when you have 2 users with the same name? – ennuikiller Mar 17 '13 at 12:32
  • Are the names unique? Is the `name` a `PRIMARY KEY`? – Michael Berkowski Mar 17 '13 at 12:32
  • They are unique, but are not primary keys, the recommendations on other questions seems to be to have an integer primary key. – w4etwetewtwet Mar 17 '13 at 12:34
  • yes, but that's not essential. You want a unique value, and even if the values are unique now, whats happens if you get a user with the same name as an existing user? You can uniquefy the new users name by appending a digit to it, buts that kind of hacky .... – ennuikiller Mar 17 '13 at 12:37
  • @ennuikiller It's already built into the php front end that a user can't create an account with the same name. – w4etwetewtwet Mar 17 '13 at 12:40

2 Answers2

4

One of the main reasons why you should have a Primary ID is because it should be permanent value since it could be used for referencing foreign keys from the other tables.

You can use name provided that:

  • There is a key defined on it. (obviously, a Primary Key or a Unique Key)
  • It cannot be modified. You have stated in your question: "..associating I could just use the name of the users to associate with..". What if you want to modify the name and there are already records that are depending on it?
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • you forgot to mention that if he uses name field he has to insure that it contains unique values – ennuikiller Mar 17 '13 at 12:35
  • Given the fact that the name is probably going to be the users email they may well want to change it (I didn't think of that). So looks like I'll go down the unique name/email and primary integer route. – w4etwetewtwet Mar 17 '13 at 12:37
  • 1
    One more thing: [Is there a REAL performance difference between INT and VARCHAR primary keys?](http://stackoverflow.com/questions/332300/is-there-a-real-performance-difference-between-int-and-varchar-primary-keys) – John Woo Mar 17 '13 at 12:40
0

You could define name as primary key. The result: can not be two users with same name in your table. The DB engine find records faster when you search by name.

Or you could add an integer primary key. and make name field as unique. In this case the above two think is exists. And one other thing: if you search records by integer (id) the DB engine find more faster the records.

Kovge
  • 2,019
  • 1
  • 14
  • 13