MySQL mapping fields

Question

I have a table where I put tons of unique numbers from 1 to 9999999 In order.

It has 3 fields. idnum,username,t

I set t to 1 when I use an idnum so that no one else can have that for a member id number.

I want to put username as the only place we put the username because we made it where people can change their username. And that was never considered when I programmed the website, because the owners told me they would not allow it, but they had overwhelming requests for it so they allowed it.

The problem is that in 9 years worth of programming, the first 6 I used username in most.

I can go add idnum to those tables and update it. My question is, CAN I update the tables to get the username in those tables to pull the username data from that table above where the idnum matches?

Kind of like mapping it, like Microsoft's "Access" does.

Is that possible? If so where do I find that documentation on how to do it?

My goal is to do that so that if someone changes their use name changing it in that table would update it in all of them.

best regards, -Richard

Answer 1

Yes, it's common to keep the username in one table, and then reference it by the id associated with that username. Then the idnum stays the same in all your tables, and you can change the username in one place.

You need to add idnum to each table where you store username.

ALTER TABLE favoritecolors ADD COLUMN idnum INT;

The idnum will be NULL by default after you create this new column.

Then update it to the idnum value matching the corresponding username:

UPDATE TABLE favoritecolors AS f
JOIN usernames AS u ON f.username = u.username
SET f.idnum = u.idnum;

If any of your tables contained a username that is not found in the usernames table, it won't update the idnum. So the idnum will still be NULL. Search to make sure this didn't happen:

SELECT username FROM favoritecolors WHERE idnum IS NULL;

This should return no rows.

After you map all your rows with a valid idnum, you can choose to drop the superfluous username from the dependent table, so the username is maintained in just one table (usernames).

ALTER TABLE favoritecolors DROP COLUMN username;

Make sure you first update all application code that queries username from any of your tables, so that it queries the idnum instead!