Menu
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?
283
You should put an attribute in a separate table whenever you expect that one person could have multiple of that attribute. Otherwise, there's not much reason to separate it, and there can be some conceptual overhead in doing so.
In many cases, it may be best to split information into multiple related tables, so that there is less redundant data and fewer places to update.
In a simple database, you might have only one table. For most databases you will need more than one. For example, you might have a table that stores information about products, another table that stores information about orders, and another table with information about customers.
Generally, a relationship is established by linking these key fields between tables – the primary key in one table and a foreign key in another table. Every table should have a primary key – one or more fields whose contents are unique to each record. This is called entity integrity in the database management.
There are several points that are definitely not 3NF; and some questionable ones in addition:
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.
87
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
