Domain Modeling: Exercise I - solution

You can find the exercise description here.  Below is one possible solution.

These are the entities we came up with, based on the current user stories/wireframes:

• Salesperson – the salesperson using the system
• Contact  – a person that I have sales interactions (activities) with
• Company – the company that a Contact works for
• Activity – the interaction (like a call or email) we have with a Contact
• Industry – the market (like "Consumer Electronics") a company can belong to

If we begin our data model design, it starts by looking something like this:

CREATE TABLE salespeople (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  first_name TEXT,
  last_name TEXT,
  email TEXT
);

CREATE TABLE contacts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  first_name TEXT,
  last_name TEXT,
  email TEXT,
  phone_number TEXT
);

CREATE TABLE companies (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT
);

CREATE TABLE activities (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  occurred_at TEXT,
  notes TEXT
);

CREATE TABLE industries (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT
);

And we also have these relationships:

• A one-to-many relationship between Company and Contact; that is, a company can have many contacts, but a contact belongs to only one company
• A one-to-many relationship between Salesperson and Activities; that is, a salesperson can have many activities, but a single activity belongs to only one salesperson
• A one-to-many relationship between Contact and Activities; that is, a contact can have many activities, but a single activity belongs to only one contact
• A many-to-many relationship between Company and Industry; that is, a company can be categorized into multiple industries, and an industry can have many companies

As we learned in the last lesson, from an implementation perspective, a one-to-many relationship is built by adding a foreign key column to the "many" side of the equation:

CREATE TABLE salespeople (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  first_name TEXT,
  last_name TEXT,
  email TEXT
);

CREATE TABLE contacts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  first_name TEXT,
  last_name TEXT,
  email TEXT,
  phone_number TEXT,
  company_id INTEGER
);

CREATE TABLE companies (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT
);

CREATE TABLE activities (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  occurred_at TEXT,
  notes TEXT,
  salesperson_id INTEGER,
  contact_id INTEGER
);

CREATE TABLE industries (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT
);

And, a many-to-many relationship is actually two one-to-many relationships put together with a join model; in this case:

• A Company has one-to-many Industries
• An Industry has one-to-many Companies

What should we call the join model?

There are only two hard things in Computer Science: cache invalidation and naming things.

– Phil Karlton

Many developers like to reference this quote – a lot. Naming things is indeed very hard. What's a word we can use to describe a company/industry combination? Turns out that there really isn't one – not one that the majority of people use and would understand right away and without much explanation. So we're simply going to refer to this as an Industry Membership. Our final domain model, implemented in SQL, looks like this:

CREATE TABLE salespeople (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  first_name TEXT,
  last_name TEXT,
  email TEXT
);

CREATE TABLE contacts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  first_name TEXT,
  last_name TEXT,
  email TEXT,
  phone_number TEXT,
  company_id INTEGER
);

CREATE TABLE companies (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT
);

CREATE TABLE activities (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  occurred_at TEXT,
  notes TEXT,
  salesperson_id INTEGER,
  contact_id INTEGER
);

CREATE TABLE industries (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT
);

CREATE TABLE industry_memberships (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  company_id INTEGER,
  industry_id INTEGER
);