Creating a database

[Achupa]

Hi, I have this problem to solve

Create a set of tables to store data for an address book (contact) application.
Bare in mind the following design criteria.

* The application will be used by multiple people so the database should also store a list of users.
* The people (contacts) in the database will be owned by only one user.
* Each contact may have multiple addresses (home, work etc.)
* Each contact may have multiple phone numbers.
* Each contact can have multiple events associated with them e.g. birthday, anniversary, meeting etc.
* The types of address, phone number and event must be limited to a list held in a separate table. This will avoid users abbreviating or making typing errors and make it possible to accurately retrieve all birthdays for example.

When designing your database consider the best data type for each column, the best use of indexes and keys.

Your submission should include a script containing all the commands required to create your database and a diagram explaining the relationships between each of the entities (tables) - an Entity Relationship Diagram.

My solution is as below:

CREATE DATABASE IF NOT EXISTS my_address_book;

USE my_address_book;

DROP TABLE IF EXISTS person;
CREATE TABLE person(
p_id integer primary key,
first_name varchar(100),
last_name varchar(100)
);

DROP TABLE IF EXISTS event_type;
CREATE TABLE event_type(
et_id integer primary key,
event_type varchar(100)
);

DROP TABLE IF EXISTS event;
CREATE TABLE event(
event_id integer primary key,
p_id integer,
et_id integer,
event_date varchar(20),
FOREIGN KEY (p_id) REFERENCES person (p_id) on delete cascade,
FOREIGN KEY (et_id) REFERENCES event_type (et_id) on delete cascade
);

DROP TABLE IF EXISTS phone;
CREATE TABLE phone(
pno_id integer primary key,
p_id integer,
phone_number varchar(100),
FOREIGN KEY (p_id) REFERENCES person (p_id) on delete cascade
);

DROP TABLE IF EXISTS address_type;
CREATE TABLE address_type(
at_id integer primary key,
address_type varchar(100)
);

DROP TABLE IF EXISTS address;
CREATE TABLE address(
a_id integer primary key,
at_id integer,
p_id integer,
address varchar(100),
ps_code varchar(20),
town varchar(100),
country varchar(100),
FOREIGN KEY (p_id) REFERENCES person (p_id) on delete cascade,
FOREIGN KEY (at_id ) REFERENCES address_type (at_id ) on delete cascade
);

DROP TABLE IF EXISTS contacts;
CREATE TABLE contacts(
c_id integer primary key,
a_id integer,
p_id integer,
pno_id integer,
event_id integer,
FOREIGN KEY (p_id) REFERENCES person (p_id) on delete cascade,
FOREIGN KEY (a_id ) REFERENCES address (a_id ) on delete cascade,
FOREIGN KEY (pno_id) REFERENCES phone (pno_id) on delete cascade,
FOREIGN KEY (event_id ) REFERENCES event (event_id ) on delete cascade
);

However, my tutor tells me

Your solution is good but it has some weaknesses in database design. You might have misunderstood the requirements.
Assume you have a system that's used by multiple users. Each user uses the system to store data about their contacts. The contacts here refer to people who are important to the user (friends, relatives, e.tc.) Each of these contacts has phones, events and addresses, that the user needs to store in the database.
Therefore:
- Events, addresses and phones belong to contacts, not the user (person in your case), as you had indicated.
- You relate the contacts vs (address,events,phones) wrongly. If a particular contact has multiple phones for example, it would be difficult to accommodate those phones in the database.

Your diagram needs to reflect all the features of the database. It should show all the tables and their fields.

How can I do this?

[omrsafetyo]

Your phone numbers and addresses should have foreign keys to the contact table - not to the person table. e.g. Swap out p_id for c_id. THis is what he means by
"- Events, addresses and phones belong to contacts, not the user (person in your case), as you had indicated."

Conversely, the layout of the "contacts" table does not currently allow you to have multiple phone numbers or addresses associated with it. Each entry in contacts only allows one associated phone or address. By putting the foreign key constraint on the phone and address table, you are able to have multiple entries with the same c_id.

So e.g.

select phone_number from phone where c_id = '20715'

Will return all phone numbers for the contact 20715.

This is what he means by "You relate the contacts vs (address,events,phones) wrongly. If a particular contact has multiple phones for example, it would be difficult to accommodate those phones in the database."
You in order to have multiple phone numbers, you would have to have multiple records in the contact table, which defeats the purpose of a relational database.

You might also add a "type" column to phone and address - this way you can differentiate between a work # or a home #. You would then re-write your query as:

select phone_number, type from phone where c_id = '20715'

Results:
1203555555 Work
12035555454 Home
12035559999 Cell

[peter_budo]

Small recommendation for the future, use full names instead of 1-2 characters names. Once you start working on real projects and have to return to something you designed year ago for example you will have hard time to work out connections between tables.
Isn't address_id easier to read then a_id or phoneNum_id then pno_id?

[omrsafetyo]

I second that recommendation.

[verruckt24]

Yes I recommend that too since I am not able to read your design properly due to confusing names. I would be in a better position to tell you your way around once I am clear with your database design.
I present here my suggestions from whatever I have understood of your db design :
1. What exactly are you achieving by placing a phoneNo_Id in the contacts table that refers to a phoneNumber in the master table ?
Ans : Nothing.
You could have as easily place the phone number itself in the contacts table.
2. Do you really think a phone number requires a unique identification ?
Ans : No. (You may think o/w but it doesn't really require)
Isn't a phone number unique enough by itself - (the phone company certainly would not allocate the same phone number to two different persons)

You are doing the same thing in the case of the Addresses. Check your design, by actually creating a different table for address and then putting an Id for that address you aren't allowing a contact to have multiple addresses whereas that's the exact thing that the professor asked you to do.

Based on this observations I feel that you have not correctly understood the concept of normalization of database. Reading these concept is central to being a good db designer, I would recommend reading the same. Any good db book would have an explanation for them.

I explain here what instead you should have done to allow multiple phone numbers and addresses for a single contact. This is supposed to provide you an insight into where you are going wrong.
To understand the design, Contacts and addresses have a one-many relationship since a single contact can have multiple addresses. In such cases you always create another table for the entity that represents "many" and put an Id for the entity that represents "one" wherever you want to link the two.
For e.g. You have a ContactId field in the Address table and put in the Id for the Contact whenever an address for that particular contact is inserted. This way you would be able to store multiple addresses for the same contact.