Is it better to have userID or username in updatedBy field in database

Question

I have fields in my almost all tables like createdBy or updatedBy. I think that's just for reference.

Do you think I should enter username there or userID. Because if i need to look at database directly then that can give better understanding or it's a bad practice.

userid because you'll find it easy to relate and follow this for all your tables or else you wont be able to join using these fields — Mr. Alien, Jul 25 '12 at 05:54
Always the user ID as it is the one reference to the user account that will never change (presumably if you have a separate user name field, that one *can* change) — Pekka, Jul 25 '12 at 05:54

Ibrahim Azhar Armar · Accepted Answer · 2012-07-25T06:06:12.253

always use foreign keys to store referential records, that is userID in your case.

and regarding the approach on how to store, it depends on what you need.

a) if you want to know who last updated the record. then you should create a userID column in the table.

it is always good to store foreign keys instead of other records, because this way you can relate and fetch all the records of a user. this approach will have a limitation though, since you can store only one userID, you can only know who last updated it.

b) if you want to store all the records, to know which user updated the records and when, then you should store it in one to many relationship table. for example

user_log with columns user_id, update_datetime and perhaps a message column, telling what did the user do.

score 0 · Answer 2 · answered Jul 25 '12 at 05:54

0

UserID. Because they are smaller and faster than usernames.

suppose your users want to change the username then you will not need to update all tables which is extremely efficient

answered Jul 25 '12 at 05:54

bugwheels94

30,681
3
39
60

score 0 · Answer 3 · answered Jul 25 '12 at 05:56

0

always use ID's to keep a normalized relational data structure. this will provide better performance and much more scalability. If you can include constraints it will make it that much more cleaner.

answered Jul 25 '12 at 05:56

Johnny

1,141
9
6

score 0 · Answer 4 · answered Jul 25 '12 at 05:57

0

It is not always bad. Defends on you application need. Normalisation is good to remove redundency. Whereas if speed is the factor you can keep it as it is. As join takes time. Also inserting data means inserting into two tables.

Never the less, always +1 for normalisation, by the book :)

answered Jul 25 '12 at 05:57

Rituparna Kashyap

1,497
13
19

The table `(USER_NAME PK, other fields...)` is not less "normalized" than `(USER_ID PK, USER_NAME AK, other fields...)`. Surrogates are not really about normalization - they are about practical implementation. – Branko Dimitrijevic Jul 25 '12 at 09:09

score 0 · Answer 5 · answered Jul 25 '12 at 05:58

Use something that cannot be changed later. Usually that is true of the user_id.

In special cases you may want to store the name in addition (to be able to display the name of the user at the time, before she married, or the name of a user that has since been deleted). But normally, you query the database again for the (current) name (which can also be cached easily).

score 0 · Answer 6 · edited May 23 '17 at 12:25

0

In the case of usernames, surrogate keys tends to be a better choice. So, in your case, FKs (createdBy and updatedBy) will reference the surrogate key (userID) and not the natural key (username).

However, this doesn't mean surrogate is always better than natural key: consider this list of criteria.

edited May 23 '17 at 12:25

Community

1
1

answered Jul 25 '12 at 09:03

Branko Dimitrijevic

50,809
10
93
167

Is it better to have userID or username in updatedBy field in database

6 Answers6