Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

enter image description here

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

share|improve this question
1  
See this similar problem: Many to Many and Weak Entities – ypercubeᵀᴹ Sep 9 '13 at 11:06
    
Are domains assigned to hosts and contacts? And for every domain, the user of its host and the user of its contact should be one and the same (user)? – ypercubeᵀᴹ Sep 9 '13 at 11:09
    
In other words: Say that I am a contact of a particular user. Can I get a domain that belongs to a different user? – ypercubeᵀᴹ Sep 9 '13 at 11:12
    
Then the linked question above provides an answer. One last question. Can you have domains that are "free", not assigned (to hosts or users)? – ypercubeᵀᴹ Sep 9 '13 at 11:17
    
Ok,although I'm little bit confused, I'll try to explain to you. The difference I see with the link you provided to me is that it has many artists to many albums while mine domains,hosts,contacts are on one user and only. As for your last question domain can only exist without host. – giannis christofakis Sep 9 '13 at 11:23
up vote 5 down vote accepted

This is a rather common problem, when the design has a "diamond" shape. With MySQL, I'd use something like this:

(0) 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).

(1) The extra UNIQUE constraints in tables hosts and contacts are needed for the foreign keys from the domains table.
(2) 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)
) ;
share|improve this answer
1  
Would you mind adding some explanation, I'm struggling to spot the difference between the diagram in the question and what's in your sql. Thanks. – Tim Abell Sep 25 '15 at 12:33
1  
@TimAbell added some comments on what is changed from the question. – ypercubeᵀᴹ Sep 25 '15 at 12:53
    
thanks, much clearer :-) – Tim Abell Sep 25 '15 at 13:26
1  
It doesn't matter if the composite key is primary or unique. If the columns are not null, there is no difference. Either can be referenced by foreign keys from other tables. – ypercubeᵀᴹ Sep 26 '15 at 19:58
1  
The choice of primary key matters only in the specific implementation (MySQL's InnoDB engine uses the primary key as clustered key of the table). But that is about the physical design of the table, not the logical design. – ypercubeᵀᴹ Sep 26 '15 at 20:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.