Member Address Normalization

Question

I'm just starting to work with the database, and I'm having problems getting normalization with the member_address entitiy. It seems I cannot post the image due to lack of reputation, so I will try to explain my tables.

Participant (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 do not quite understand how to approach this. I thought I needed two separate tables in order to display the data correctly, but it looks like my PK-nad FK is not quite correct here. Is it better to have a table full of states and cities? Or get a zip code to search for a city? Pretty lost here ...

Answer 1

In the real world, people can use the same address, and one person can have more than one address. In addition, people can move (not sure if this is important for your model), so the relationship between the person and the address should have date_from / date_through attributes (again, this may not be important in the context of your application, so you can skip this part ), So I would 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)
town
- other attributes such as address_line_1, unit_number, postal_code, etc.

** Note: for simplicity, I store state_id and country_id here, which in a sense violates normalization. However, you can let people not enter the state, not in all countries.

Member_Address :
member_address_id PK
member_id FK
FID_address
date_from
date_thru
UNIQUE (member_id, address_id, date_from)

In addition, you can add an Address Purpose object and add a link between the address assignment and the participant’s address (for example, if you need to distinguish between home address / work address / mailing address).

Further, you will see that the mailing address, phone and email are all means of communication, so they can all be considered as a subtype of a common entity, for example communication_mechanism ...

Answer 2

If you look at http://en.wikipedia.org/wiki/Database_normalization , there are many interesting points regarding normalization. You should definitely study different degrees of normalization.

In your case, you have members, and each member has addresses. He meant in design that a member can have only one address. Similarly, your design implies that a member has only one telephone and only one email address. You can handle this in many different ways, but for starters, you probably look at 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, breaking the second normal form, as I assume that the city and state are implicit in the Zip code. When you save the attributes "Phone" and "Email" in the table, you break the normalization because your project cannot process several phone numbers (Home / Work / Cell) or email addresses.

This is often and pragmatically solved by adding a number of additional attributes, solving an immediate problem, but maintaining a normalization violation. A clean / correct solution would be to put this information on a separate side table in the same way as existing addresses, and then associate it with the FK / PK ratio.

Answer 3

Start by thinking in terms of entities and relationships (conceptual design) instead of directly building a physical model (tables and indexes); you describe a kind of hybrid in your question.

Then define the attributes of each object that uniquely identify each instance. This is the primary key (candidate, natural). For each object, determine the presence of additional (candidates, natural) primary keys.

Then define each relationship between your entities, with it the corresponding foreign keys. For a master-detail relationship such as here, define a set of attributes in Entity details that map to the primary key (candidate, natural) in the main entity and thus contain a foreign key for this relationship.

Now you are ready to develop tables and indexes of the physical model of your conceptual design.