Improving object-to-data mapping
The Small Business Development Center client
table was previously defined without the inclusion of a point of contact for the client. The initial instinct of the database team was to simply add contact_name
and contact_email
columns to the client table. However, you object to this plan due to your instincts regarding proper data organization. In the future, a contact might be referenced in multiple tables. In this exercise, you will define table structures for the client and contact information that better separates the client
and contact
objects.
Recall the previous definition of the client
table:
CREATE TABLE client (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
site_url VARCHAR(50),
num_employees SMALLINT,
num_customers INTEGER
);
This exercise is part of the course
Creating PostgreSQL Databases
Exercise instructions
- Create a
contact
table with columnsid
(a primary key),name
(max length of 50), andemail
(max length of 50). - Alter the
client
table by adding acontact_id
column as a foreign key.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Create the contact table
___ ___ ___ (
-- Define the id primary key column
___ SERIAL ___ ___,
-- Define the name column
___ ___(50) NOT NULL,
-- Define the email column
___ VARCHAR(___) NOT NULL
);
-- Add contact_id to the client table
ALTER TABLE ___ ADD ___ INTEGER NOT NULL;
-- Add a FOREIGN KEY constraint to the client table
ALTER TABLE ___ ADD CONSTRAINT fk_c_id FOREIGN KEY (___) REFERENCES ___(id);