Get startedGet started for free

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

View Course

Exercise instructions

  • Create a contact table with columns id (a primary key), name (max length of 50), and email (max length of 50).
  • Alter the client table by adding a contact_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);
Edit and Run Code