Groups

    Databases - One to One Relationships

    Hello

    After some advice, what would be better:

    having 2 tables in a database called; 'Users' and another called 'PersonalDetails'.

    In Users there will be an ID, username, password

    In Personal Details there will be ID, usersID, firstname, surname, DoB, address

    and link the 2 tables using the users.ID with the personalDetails.usersID

    OR


    Contain all them details in one table called 'Users' thus not having to link.

    My initial thought were to keep details seperate but really there will be only one personalDetails record per users record so is there any point in seperating the two pieces of info?

    9 Comments

    Banned

    keep it in one. and why do you have username, ID and userID? just keep username as the primary key.

    and encrypt the password.
    Edited by: "bykergrove" 4th Nov 2010

    If you're only going to be using the data in the personalDetails table for users i'd say put it all in one table. There would be little point in making two in my humble opinion

    Original Poster

    bykergrove

    keep it in one. and why do you have username, ID and userID? just keep … keep it in one. and why do you have username, ID and userID? just keep username as the primary key.and encrypt the password.



    In the users table there is:

    ID = this auto increments and is set as primary key

    Username = is username and has a unique index set

    Password = is password is md5'd and salted

    The reason why im linking using users.ID and personalDetails.UserID is because if a user wants to change a username then it will only need changing in USERS table and not throughout the whole DB where the username is stored


    Edited by: "mds1256" 4th Nov 2010

    Banned

    mds1256

    In the users table there is:ID = this auto increments and is set as … In the users table there is:ID = this auto increments and is set as primary keyUsername = is usernamePassword = is passwordThe reason why im linking using users.ID and personalDetails.UserID is because if a user wants to change a username them it will only need changing in USERS table and not throughout the whole DB where the username is stored



    ok fair enough, if you allow the user to change their username then keep the primary key as ID.
    It doesn't make sense to keep the username/password separate from personal details.

    I would still recommend merging the two tables.

    also, it's good practice to include created date, modified date and a soft delete flag
    Edited by: "bykergrove" 4th Nov 2010

    Original Poster

    bykergrove

    ok fair enough, if you allow the user to change their username then keep … ok fair enough, if you allow the user to change their username then keep the primary key as ID. It's doesn't make sense to keep the username/password separate from personal details.I would still recommend merging the two tables.



    Ok many thanks for the advice, thinking about it, it does seem sensible to do so, no point in slowing a query down by making it look in 2 places for the info that could be held in one

    Banned

    mds1256

    Ok many thanks for the advice, thinking about it, it does seem sensible … Ok many thanks for the advice, thinking about it, it does seem sensible to do so, no point in slowing a query down by making it look in 2 places for the info that could be held in one



    I added some more information to my post above.

    Original Poster

    yeah i've seen.

    actually in my users table i have a few more columns for things like that e.g.

    last login date
    created date
    active
    paid member
    lockedout
    login attempts

    etc etc

    Banned

    mds1256

    yeah i've seen.actually in my users table i have a few more columns for … yeah i've seen.actually in my users table i have a few more columns for things like that e.g.last login datecreated dateactivepaid memberlockedoutlogin attemptsetc etc



    ok well the table seems like it is a little large then...

    it's down to preference and simplicity of design. you could split account information and personal details into two tables but it wouldn't make a massive amount of difference with the volumes you'll likely be experiencing.

    If you are worried about performance think about appropriate indexing:
    http://en.wikipedia.org/wiki/Index_(database)

    haha this is a blast from the past for me

    Banned

    Inb4 database expert theSaint
    Post a comment
    Avatar
    @
      Text
      Top Discussions
      1. Jury service1823
      2. Iphone 7 - What Case?616
      3. cheapest way to Get from BRIDLINGTON TO ACCRINGTON ??44
      4. Large card print/poster..11

      See more discussions