Databases - One to One Relationships - HotUKDeals
We use cookie files to improve site functionality and personalisation. By continuing to use HUKD, you accept our cookie and privacy policy.
Get the HUKD app free at Google Play

Search Error

An error occurred when searching, please try again!

Login / Sign UpSubmit

Databases - One to One Relationships

mds1256 Avatar
6y, 4w agoPosted 6 years, 4 weeks ago
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?
mds1256 Avatar
6y, 4w agoPosted 6 years, 4 weeks ago
Options

All Comments

(9) Jump to unreadPost a comment
Comments/page:
banned#1
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 on Nov 04, 2010 19:33: .
#2
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
#3
bykergrove
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 on Nov 04, 2010 19:39: spelling
banned#4
mds1256
bykergrove
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

Password = is password

The 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 on Nov 04, 2010 19:43: .
#5
bykergrove
mds1256
bykergrove
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

Password = is password

The 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'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#6
mds1256
bykergrove
mds1256
bykergrove
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

Password = is password

The 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'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


I added some more information to my post above.
#7
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#8
mds1256
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


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#9
Inb4 database expert theSaint

Post a Comment

You don't need an account to leave a comment. Just enter your email address. We'll keep it private.

...OR log in with your social account

...OR comment using your social account

Thanks for your comment! Keep it up!
We just need to have a quick look and it will be live soon.
The community is happy to hear your opinion! Keep contributing!