Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2013

    Question Unanswered: Table Relationships

    I'm working on a project that has a project table and a contact table. Each project could have many contacts and each contact could be assigned to more than one project at a time. I'm confused on how to establish the relationships between these 2 tables. Could anyone offer some assistance?

    Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    In SQL, you can have 1-to-many links using a foreign key. This is a pretty common kind of relationship and one that SQL makes pretty easy.

    You want a many-to-many link, which is a lot harder until you figure out the SQL idiom for handling this problem.
    CREATE TABLE contacts (
       contactId        INT             NOT NULL
    ,  firstName        VARCHAR(20)         NULL
    ,  lastName         VARCHAR(20)     NOT NULL
       PRIMARY KEY (contactId)
    CREATE TABLE projects (
       projectId        INT             NOT NULL
    ,  name             VARCHAR(20)     NOT NULL
       PRIMARY KEY (projectId)
    CREATE TABLE contacts_projects (
       contactId        INT             NOT NULL
       FOREIGN KEY (contactId)
          REFERENCES contacts (contactId)
    ,  projectId        INT             NOT NULL
       FOREIGN KEY (projectId)
          REFERENCES projects (projectId)
    ,  dateBegin        DATE            NOT NULL
    ,  dateEnd          DATE                NULL
       CHECK (dateBegin <= dateEnd OR dateEnd IS NULL)
       PRIMARY KEY (contactId, projectId)
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts