Database: Splitting Tables
I've recently read alot about databases, redundancy, etc.
Now I'm building an users-table which will hold all informations about the users registered at my system.
The users-table should hold: id(PK), username, password, email, and the address(street, city, zipcode, country).
Should I split the address and create another table only holding the users-address, like: id(PK), street, city, zipcode, country, user_id(FK)) ? I even could split here the zipcode and country to new tables.
Does this make sense, especially the splitting of zipcode and country to a new table ?
开发者_C百科Cheers!
Well I thought about some redundancy-aspects here. Wouldn't it be better to store e.g. the country in a single table instead of writing it over and over again in my users-table? -> redundancy
For most cases the answer is no. Why have an extra join to find the address? And why have two tables with one-to-one relationship?
As other folks said, don't complicate your life unless you are sure about some performance gains.
I personally don't see any added value on splitting these details into separated tables.
Even if you are not likely to use the address/zipcode very often, you can always select only the necessary fields using SELECT username,email..
instead of SELECT *..
You should do this based on a requirement, not because you can. So, either you should have a functional need (e.g., you want to store multiple addresses per user) or a technical issue that you want to solve (performance, security, etc.). To me, the latter sounds rather unlikely on such a straightforward table design, so with the information you are providing, I would suggest sticking with a single table.
精彩评论