SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard Wolf_22's Avatar
    Join Date
    Jul 2005
    Posts
    1,714
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Basic Question(s) - Relationships

    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.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    How many profiles can a user have? If it's only one, then do you need a separate profile table?

  3. #3
    SitePoint Wizard Wolf_22's Avatar
    Join Date
    Jul 2005
    Posts
    1,714
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    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

  4. #4
    SitePoint Enthusiast erangalp's Avatar
    Join Date
    Oct 2010
    Location
    Israel
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SitePoint Wizard Wolf_22's Avatar
    Join Date
    Jul 2005
    Posts
    1,714
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    SitePoint Enthusiast erangalp's Avatar
    Join Date
    Oct 2010
    Location
    Israel
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, you need to add the user_id to the insertion query (through PHP) for the profiles table.

  7. #7
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  8. #8
    SitePoint Wizard Wolf_22's Avatar
    Join Date
    Jul 2005
    Posts
    1,714
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •