Constrains relationship

Question

I have the following design. Each user has it's own contacts, it's own hosts and assign those to a domain which belongs only to him.

I want to insure that the host you assign to the domain belongs to the user that domain is assigned to.

Is it something that I have to insure in my application logic?

If I set domains.user_id foreign key to reference host.user_id and user_id.id does it eliminate my issue?

By the way can anyone suggest me a better design for my schema?

Answer 1

This is a rather common problem, when the design has a "diamond" shape. See similar questions:
Many to Many and Weak Entities

With MySQL, I'd use something like this:

⁽⁰⁾ Note that I prefer user_id as name for the primary key of users and not id for all the tables. I find the SQL code totally confusing otherwise (plus you can use the JOIN ... USING (tablename_id) syntax).

⁽¹⁾ The extra UNIQUE constraints in tables hosts and contacts are needed for the foreign keys from the domains table.
⁽²⁾ The two FOREIGN KEY constraints from the domains table are changed to use composite keys (include the user_id).

Table users

CREATE TABLE users
( user_id INT NOT NULL AUTO_INCREMENT
, username VARCHAR(45) NOT NULL
-- other columns
, PRIMARY KEY (user_id)
) ;

Table contacts

CREATE TABLE contacts
( contact_id INT NOT NULL AUTO_INCREMENT
, user_id INT NOT NULL
, info TEXT
, PRIMARY KEY (contact_id)
, UNIQUE INDEX (user_id, contact_id)       -- added, see comment 1 above
, INDEX (user_id)
, FOREIGN KEY (user_id)
    REFERENCES users (user_id)
) ;

Table hosts

CREATE TABLE hosts
( host_id INT NOT NULL AUTO_INCREMENT
, user_id INT NOT NULL
, name VARCHAR(45) NOT NULL
--
, PRIMARY KEY (host_id)
, UNIQUE INDEX (user_id, host_id)          -- added, see comment 1 above
, INDEX (user_id)
, FOREIGN KEY (user_id)
    REFERENCES users (user_id)
) ;

Table domains

CREATE TABLE domains
( domain_id INT NOT NULL AUTO_INCREMENT
, user_id INT NOT NULL
, contact_id INT NOT NULL
, host_id INT NULL                       -- nullable based on comments
, name VARCHAR(45) NOT NULL
-- other columns
, PRIMARY KEY (domain_id)
, FOREIGN KEY (user_id, contact_id)       -- composite FK, see comment 2
    REFERENCES contacts (user_id, contact_id)
, FOREIGN KEY (user_id, host_id)          -- composite FK, see comment 2
    REFERENCES hosts (user_id, host_id)
) ;