Normalizing member address
database, sql
Solution
In real world, people can share the same address, and one person can have more than 1 address. Also, people can move (not sure if that matters for your model), so relationship between person and address should have date_from/date_through attributes (again, it may be not important in the context of your application, so you can skip this part). Thus, I'd go with something like
Country: country_id (PK) name
State: state_id (PK) name country_id (FK)
Address: address_id (PK) state_id (FK) country_id (FK) city --other attributes, like address_line_1, unit_number, postal_code etc...
**Note: for simplicity I store state_id and country_id here, which in a sense breaks normalization . However, you may want to allow people not to enter state, and not all countries have states.
Member_Address: member_address_id PK member_id FK address_id FK date_from date_thru UNIQUE(member_id,address_id,date_from)
Also, you may want to add Address Purpose entity, and add a relationship between address purpose and member address (say, if you need to differentiate home address/work address/mail address).
Going further, you will see that postal address, phone, and email are all communication means, so they all can be treated as subtype of common entity,for instance communication_mechanism...
Problem
I am just starting out with database and I am having trouble getting the normalization corred with a member_address entitiy. I don't seem to be able to post a picture due to having no rep so I will attempt to explain my tables. Member (Table) PK Member_ID FK Member_Zip_Code FK Membership_Type_code ATT: First_Name ATT: Last_name ATT: Member_Phone ATT: Member_Email Member_Address (Table) PK Member_Zip_Code FK Member_ID ATT: Member Address ATT: Member_State ATT: Member_City I don't quite understand how to approach this. I was thinking that I needed two seperate tables to show the data correctly but it would seem my PK nad FK's are not exactly correct here. Is it best to have a table full of States and Cities? Or have a zip code lookup the city? Pretty lost here...