Tutorial 3: Creating A Relational Data Model

For my third tutorial, I opted to take the Database assignment an create an actual small third normal form database based on this one table. I used Erwin to create an Logical Model also know as an Entity Relationship Diagram. (ERD)

The steps needed to normalize and expand a database from flat file data begin as follows

1. Profile the existing data. Evaluate what was truly dependent on the True Primary Key of the members table. It is my opinion that the true primary key was a compound key of the First Name + Last Name + Birth date

2. I looked to see if there were ways to make the data entry easier while also creating better data integrity. That led me to create a state table that could be a pull down menu from the entry forms.

3. I decide that individuals could have more then one address, so I created a Physical address table that would record all street addresses. I created an associative table that would allow an address to belong to multiple members (Family Membership) and allow each member to have more then one address (Home, Office, Dorm)

4. I thought that it might be important to store the member e-mail address. I decided that individuals could have more then one email address, so I created a email address table that would record all email addresses. I created an associative table that would allow an e-mail address to belong to multiple members (Family Membership) and allow each member to have more then one email address (Home, Office, Junk)

5. I created a Membership Transaction Table. I did this so that we could tract all of the different interaction that we have with a member. The table we have only tracked joined and left. I created a transaction type reference table that will store Joined and Left but also Renewed, Rejoined, Suspended, or any other type of transaction that a member might do.

6. The transaction table it associated to the member through the email and address table so that we can run reports based on the address and e-mail address the member used for the transaction.



This ER Diagram is an original work of Theresa Fortnash.

It is always best to have a business requirements document and access to a Subject Matter Expert (SME) when designing or modifying a database as there may be many hidden business rules that may need to be accommodated in the database or explained in the metadata. Metadata is the information about the tables columns entities, attributes and the database itself. There is both technical and business metadata. The ERD should contain both.

This is a very basics lesson in the thought process of a relational data modeler. If anyone interested in learning about creating a dimensional (Star Schema) Data warehouse database from this model, send me an e-mail or a comment and I will be happy to help.