Currently my users table has the below fields
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?
-
1What happens if a user has multiple addresses (or multiple mobile phone numbers, for that matter)? Do you allow this?– Michael PetrottaCommented Jul 4, 2011 at 22:10
-
1What happens if you want to store an address for something other than a user?– razlebeCommented Jul 4, 2011 at 22:11
-
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– EnzeroCommented Jul 4, 2011 at 22:24
-
1Read 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).– Michael PetrottaCommented Jul 4, 2011 at 22:44
-
1Don't use SHA-1. Use one of the SHA-2 options, and don't forget to add salt.– Michael PetrottaCommented Jul 4, 2011 at 22:50
|
Show 3 more comments
6 Answers
There are several points that are definitely not 3NF; and some questionable ones in addition:
- Could there could be multiple addresses per user?
- Is an address optional or mandatory?
- Does the information in City, Country, Region duplicate that in Address?
- Could a user have multiple TelNos?
- Is a TelNo optional or mandatory?
- Could a user have multiple MobNos?
- Is a MobNo optional or mandatory?
- Could a user have multiple Emails?
- Is an Email optional or mandatory?
- Is NoOfMembers calculated from the count of users?
- Can there be more than one UserAttempts?
- 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.
answered Jul 4, 2011 at 22:31
-
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– EnzeroCommented Jul 4, 2011 at 22:37
-
City Address Country Region TelNo MobNo should be in another table then should i also split the other fields?– EnzeroCommented Jul 4, 2011 at 23:14
-
I would go for City Address Region as one table; and TelNo, MobNo as another. Commented Jul 4, 2011 at 23:29
-
"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. Commented Jul 5, 2011 at 0:42
-
@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....". Commented Jul 5, 2011 at 2:40
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.
answered Jul 4, 2011 at 22:12
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.
-
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, ...) Commented 2 days ago
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...
answered Jul 4, 2011 at 22:12
-
4@Enzero: Do not use the username as the primary key -- people should be able to change it as they like. Commented Jul 4, 2011 at 22:24
-
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... Commented Jul 4, 2011 at 22:25
-
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.– EnzeroCommented Jul 4, 2011 at 22:28
-
2@Enzero: StackExchange does.. ;) INT takes less space than VARCHAR, meaning joining/searching/etc on it will perform better. Commented Jul 4, 2011 at 22:29
-
1You 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... Commented Jul 4, 2011 at 22:45
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.
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.
