DaniWeb IT Discussion Community - View Single Post

Creating a database

Nov 28th, 2008

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:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
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
);
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?