Should User and Address be in separate tables?

Question

Currently my users table has the below fields

Username

Password

Name

Surname

City

Address

Country

Region

TelNo

MobNo

Email

MembershipExpiry

NoOfMembers

DOB

Gender

Blocked

UserAttempts

BlockTime

Disabled

I'm not sure if I should put the address fields in another table. I have heard that I will be breaking 3NF if I don't although I can't understand why. Can someone please explain?

Chang

What happens if a user has multiple addresses (or multiple mobile phone numbers, for that matter)? Do you allow this? – Norvall

What happens if you want to store an address for something other than a user? – Janayjanaya

The password is MD5 and a user has one mobile and doesn't need more than one address this is an online theater booking system just so you guys know. I can't think of a reason to store an address for something else can u give me some suggestions razlebe – Chang

Glad the password's hashed, but know that MD5 is a poor choice for that. – Norvall

Read the Wikipedia link in my comment, then Google for "MD5 compromised". SHA-2 is a generally-accepted alternative (and SHA-3, when it's ready). – Norvall

Don't use SHA-1. Use one of the SHA-2 options, and don't forget to add salt. – Norvall

Review the links in my comments, and review the many, many articles online on the topic. Stack Overflow's got a bunch, too. Also, if you're directing a comment at someone else, remember to prefix the comment with the 'at' sign and their name (as in, @Enzero). If you don't do that, I don't get the little red light notifying me of a comment. See some of the comments below for examples. – Norvall

The security discussion about password field is completely out of the scope of the question – Sedda

Answer 1

There are several points that are definitely not 3NF; and some questionable ones in addition:

1. Could there could be multiple addresses per user?
2. Is an address optional or mandatory?
3. Does the information in City, Country, Region duplicate that in Address?
4. Could a user have multiple TelNos?
5. Is a TelNo optional or mandatory?
6. Could a user have multiple MobNos?
7. Is a MobNo optional or mandatory?
8. Could a user have multiple Emails?
9. Is an Email optional or mandatory?
10. Is NoOfMembers calculated from the count of users?
11. Can there be more than one UserAttempts?
12. Can there be more than one BlockTime per user?

If the answer to any of these questions is yes, then it indicates a problem with 3NF in that area. The reason for 3NF is to remove duplication of data; to ensure that updates, insertions and deletions leave the data in consistent form; and to minimise the storage of data - in particular there is no need to store data as "not yet known/unknown/null".

In addition to the questions asked here, there is also the question of what constitutes the primary key for your table - I would guess it is something to do with user, but name and the other information you give is unlikely to be unique, so will not suffice as a PK. (If you think name plus surname is unique are you suggesting that you will never have more than one John Smith?)

EDIT: In the light of further information that some fields are optional, I would suggest that you separate out the optional fields into different tables, and establish 1-1 links between the new tables and the user table. This link would be established by creating a foreign key in the new table referring to the primary key of the user table. As you say none of the fields can have multiple values then they are unlikely to give you problems at present. If however any of these change, then not splitting them out will give you problems in upgrading the application and the data to support the application. You still need to address the primary key issue.

Hexagram

Ok then let me list the ans 1.only 1 address,2.optional,3.well i hope not,4. only 1,5. optional,6. only 1,7. optional,8. no,9. mandatory,10 is in relation to membership as how many tickets can be bought for free for each show,11. is a count to say until being blocked,12. a can only be blocked once at a time – Chang

City Address Country Region TelNo MobNo should be in another table then should i also split the other fields? – Chang

I would go for City Address Region as one table; and TelNo, MobNo as another. – Hexagram

"If the answer to any of these questions is yes, then it indicates a problem with 3NF in that area." 3NF has to do with transitive dependencies. It has nothing to do with whether the OP wants to store one or more addresses per user, whether a user has multiple telephone numbers, or whether a user has multiple email addresses. – Hyperphagia

@Catcall - I agree completely - the indicators I listed were all points to identifying potential transitive dependencies - I didn't want to get into an extensive definition of 3NF nor stray too far from simple guidelines based on the fields given. It would probably have been more accurate to say "it suggests that there could be ...." rather than "it indicates....". – Hexagram

I'm confused with "The reason for 3NF is to... to minimise the storage of data - in particular there is no need to store data as "not yet known/unknown/null"." I've never heard that a requirement for 3NF – Triquetrous

Answer 2

As long as every user has one address and every address belongs to one user, they should go in the same table (a 1-to-1 relationship). However, if users aren't required to enter addresses (an optional relationship) a separate table would be appropriate. Also, in the odd case that many users share the same address (e.g. they're convicts in the same prison), you have a 1-to-many relationship, in which case a separate table would be the way to go. EDIT: And yes, as someone pointed out in the comments, if users have multiple address (a 1-to-many the other way around), there should also be separate tables.

Zirconia

Answer 3

Just as point that I think might help someone in this question, I once had a situation where I put addresses right in the user/site/company/etc tables because I thought, why would I ever need more than one address for them? Then after we completed everything it was brought to my attention by a different department that we needed the possibility of recording both a shipping address and a billing address.

The moral of the story is, this is a frequent requirement, so if you think you ever might want to record shipping and billing addresses, or can think of any other type of address you might want to record for a user, go ahead and put it in a separate table.

In today's age, I think phone numbers are a no brainer as well to be stored in a separate table. Everyone has mobile numbers, home numbers, work numbers, fax numbers, etc., and even if you only plan on asking for one, people will still put two in the field and separate them by a semi-colon (trust me). Just something else to consider in your database design.

Alcoholometer

An other good reason to put contact info (phone No or Email) in separate table is 'Verification'. You can unify the verification method on all contacts info -for different entities- by putting them in separate table (single record for each single phone No) with info about verification (like Is Verified, verification code, ...) – Bill

Answer 4

the point is that if you imagine to have two addresses for the same user in the future, you should split now and have an address table with a FK pointing back to the users table.

P.S. Your table is missing an identity to be used as PK, something like Id or UserId or DataId, call it the way you want...

Plasticine

@Enzero: Do not use the username as the primary key -- people should be able to change it as they like. – Rosenberg

but not being an auto increment, does not help if you want to sort by last inserted... it's very subjective I know, personally I like to have an int DataId identity in all the tables, so I can always check which records were added last and sometimes I also have a datetime to tell me when... – Plasticine

In the case of the user table why would i care who was added last or when.In most cases i do use ints as identifiers but in this case i think user should be enough. – Chang

@Enzero: StackExchange does.. ;) INT takes less space than VARCHAR, meaning joining/searching/etc on it will perform better. – Rosenberg

please pray tell how cause i don't know how as far as i know you can only change your display name – Chang

You are fighting against the good design for no reason. Identities or Auto Increment not even have to be set in the inserts, you can ignore them at insert and you have so many advantages on having that... as other tell you, faster join and where and so on... – Plasticine

Answer 5

By adding them to separate table, you will have a easier time expanding your application if you decide to later. I generally have a simple user table with user_id or id, user_name, first_name, last_name, password, created_at & updated_at. I then have a profile table with the other info.

Its really all preference though.

Akvavit

Answer 6

You should never group two different types of data in a single table, period. The reason is if your application is intended to be used in production, sooner or later different use-cases will come which will need you to higher normalised table structure. My recommendation - Adhere to SOLID principles even in DB design.

Bribery