[RESOLVED] username or userid as primary key

sosothirsty

I want to know if it's worth it to use both a userid+username or if I could just use only username and set that as the primary key. I have been storing userid as a foreign key in other tables as well but I find that I frequently have to query the userid based on the username anyways.

How much difference would this make since the primary (and foreign) key would not be an INT? Also, I am not expecting to change usernames hardly ever. If so, is it a big deal to update all tables with the new username once in a blue moon?

tomhath

As always, the real answer is "it depends". For a small app in which the user really won't change often the shortcut is a reasonable approach. On the other had, the user table in an app like this is probably very small, so joining to it as needed shouldn't be a big deal.

I'd look into creating database VIEWs to make the queries simpler before changing the design:
http://www.techotopia.com/index.php/An_Introduction_to_MySQL_Views

Bonesnap

I personally always use IDs as primary keys.

sosothirsty

tomhath;10999305 wrote:
As always, the real answer is "it depends". For a small app in which the user really won't change often the shortcut is a reasonable approach. On the other had, the user table in an app like this is probably very small, so joining to it as needed shouldn't be a big deal.

I'd look into creating database VIEWs to make the queries simpler before changing the design:
http://www.techotopia.com/index.php/An_Introduction_to_MySQL_Views

Thanks so much for the read. I don't refer to tables multiple times in my scripts yet really but would it make sense in this instance?

I want to perform COUNT and/or SUM on some joined tables but also want to retrieve data from the rows. I don't think I can retrieve rows and do a COUNT/SUM. I could count/sum data in PHP but I believe it's more efficient to use MySQL. I'm not sure what the most efficient way to do it is and if I should be using a subquery here.

SELECT 
u.data, 
b.data 
b.things, 
b.amt, 
(SELECT SUM(amt) FROM b WHERE b.userid=u.userid) as total
FROM (SELECT userid FROM users WHERE username=?) as u
INNER JOIN b USING userid;

tomhath

I'd write it this way, but it's not necessarily better than your way:

SELECT
  u.data,
  b.data,
  b.things,
  b.amt,
  t.total
FROM u
INNER JOIN b
  ON b.userid = u.userid
INNER JOIN (
    select userid, SUM(amt) as total
    from b
    group by userid
    ) as t
  ON t.userid = u.userid

Derokorian

As far as how easy / hard it would be to update the username in other tables if you use that as the primary key; it would be very easy if you use InnoDB tables and foreign key constraints you can use ON UPDATE CASCADE. See more here -> http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

tomhath

I'm not sure about using an ON UPDATE trigger. Does MySql allow you to change the PK of a row? I've never tried, but I'd be surprised if it lets you.

Derokorian

You can. You can change any column of any row. Try it:

UPDATE `users` SET id = (SELECT Auto_increment 
FROM information_schema.tables 
WHERE table_name='users' AND table_schema = DATABASE()) WHERE `id` = 1

Then your user with id of 1 will become the next auto_increment value =D

bradgrafelman

tomhath;10999382 wrote:
I'm not sure about using an ON UPDATE trigger. Does MySql allow you to change the PK of a row? I've never tried, but I'd be surprised if it lets you.

It does only if the new value doesn't violate any constraints.

Personally, I'd still go with some internal ID value (such as an AUTO_INCREMENT'ing 'userid' numeric value) that never changes. It simplifies a lot of things for you in the end.

sosothirsty

tomhath;10999349 wrote:

I'd write it this way, but it's not necessarily better than your way:

SELECT
  u.data,
  b.data,
  b.things,
  b.amt,
  t.total
FROM u
INNER JOIN b
  ON b.userid = u.userid
INNER JOIN (
    select userid, SUM(amt) as total
    from b
    group by userid
    ) as t
  ON t.userid = u.userid

You didn't narrow down by username but I like your solution better with the SUM(), looks more efficient.

I talked myself out of making username the PK. I will read up on the CASCADE as I have been meaning too. Always helpful, thanks everyone.

tomhath

Yea, by the time I noticed WHERE u.userid=? was missing it was too late to edit.