Let’s assume I have 2 tables in a database. 1 is “user” and another is “profile.” How would I make the 2 related to one another?
For example, let’s say that Joe comes to my website, registers, and then creates his profile. In the database, the auto_increment will most likely be whatever given number he had upon registration, such as 2 or 1,241–whatever. If I wish to make each respective user align with their respective profiles, does this mean that I should set the profile table to auto_increment as well? If so, is this a best practice? Any wisdom from the pros on this is appreciated.
Anyway, I have 1 last question: how should one store physical addresses in a database? I believed that using VARCHAR(100) might be okay, but then again…
Thanks in advance.
That’s a good question, Guido. I’m laughing about this little issue as I never even thought from that perspective.
They should only have 1, but if that’s the case, this would mean then that the “user” table should probably have more fields. So far, the user table has the following structure and the reason I was thinking about making a “profile” table is because I was thinking about removing various parts of the “user” table to make it more succinct (?) -
“user” table schema (it could use some help as you’ll see):
id, BIGINT(20), NOT NULL, PRIMARY KEY, AUTO INCREMENT
username, VARCHAR(60), NOT NULL
password, VARCHAR(64), NOT NULL
first_name, VARCHAR(50), NOT NULL
middle_name, VARCHAR(50), NOT NULL
last_name, VARCHAR(50), NOT NULL
email_address, VARCHAR(100), NOT NULL
phone_number, VARCHAR(14), NULL
home_address, VARCHAR(20), NULL
last_ip, INTEGER(12), NOT NULL
specialty, VARCHAR(50), NOT NULL
is_registered, BOOLEAN, NOT NULL
date_registered, DATE, NOT NULL
is_admin, BOOLEAN, NOT NULL
rank, INTEGER(100), NOT NULL
profile_image, BLOB, NULL
bio_about, TEXT, NULL
How many profiles can a user have? If it’s only one, then do you need a separate profile table?
Yes, you need to add the user_id to the insertion query (through PHP) for the profiles table.
Thanks, erangalp. So I would then simply store the user_id into the primary key field of the profile table using PHP (or would this somehow be done through the MySQL side)? Sorry if that’s a dumb question–some of this is still kind of new I guess.
Both approaches are correct, and it’s more up to personal preference.
My personal approach is that when the users table becomes ridden with NULL fields, I extract those and move it to a profiles table. The profiles’ table primary key would be user_id and it points to the primary key of the users table, creating a 1-to-1 relationship.
Thanks for the info on this guys.
guelphdad, thanks for the info about the number thing. I never knew that nor the LAST_INSERT_ID() technique.
If you are creating a user in the main table and then the profile table at the same time, use LAST_INSERT_ID() to get the value entered in the main table and then use that in the profile table. Each mysql connection is independent of one another so you won’t have to worry about assigning the wrong value if you do it this way.
Side note, you don’t need a number in parentheses after numeric column types unless you are going to use ZEROFILL with them. INT(5) and INT(10) mean the same thing without ZEROFILL. The data type itself would determine what values can be inserted into the column. in the case of INT it is -2147483648 to 2147483647