Normalizing member address

Question

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...

Answer 1

If you take a look at http://en.wikipedia.org/wiki/Database_normalization, there are lot of interesting points about normalization. You should certainly look into the different degrees of normalization.

In your case, you have some members and each of the members have addresses. It's implied in the design that a member can have only one address. Similarly, your design implies that the member has only one phone and only one email-address. You can handle this in many ways, but for starters, you would probably look into something like:

Member (Table)
MemberID (PK)
MemberAddressID (FK)
MembershipType (FK) -- To a dictionary-table with membership types.
FirstName (ATT)
LastName (ATT)
Phone (ATT) -- (*OR* it could be placed in a separate side-table with phonenumbers)
Email (ATT) -- (*OR* it could also be placed in a separate side-table)

Member_Address (Table)
Member_ID (FK)
Member Address (ATT)
Member_Zip_Code (ATT) -- (*OR* it could be FK to a separate table with Zip-codes, states and cities)
Member_City (ATT)
Member_State (ATT)

With Member_city and Member_State you are strictly speaking violating the second normal form, as I assume that city and state are implicit in the Zip code. When keeping Phone and Email as attributes on the table, you are violating normalization, because your design is unable to handle multiple phonenumbers (Home/Work/Cell) or email-addresses.

Frequently, and pragmatically, this is solved by adding a number of extra attributes, solving the immediate problem, but maintaining the violation of the normalization. The clean/correct solution would be to put this information in a separate side-table much like the addresses already are, and then linking to that with a FK/PK-relation.

Answer 2

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...

Answer 3

Start by thinking in terms of Entities and Relations (the Conceptual Design) instead of directly building the Physical Model (tables and indices); you describe a sort of hybrid in your question.

Then, identify the attributes of each Entity that uniquely identify each instance. That is a (candidate, natural) primary key. For each entity, determine if there are additional (candidate, natural) primary keys.

Then identify each relation between your entities, with it's corresponding foreign keys. For a master-detail relationship such as here, determine the set of attributes in the detail Entity that map to a (candidate, natural) primary key in the master Entity, and thus comprise the Foreign Key for this relationship.

Now you are ready to design the tables and indexes of the Physical Model of your Conceptual Design.