Take the 2-minute tour ×
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.

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. –  yannis Sep 9 '13 at 11:23
show 6 more comments

1 Answer

up vote 4 down vote accepted

This is a rather common problem, when the design has a "diamond" shape. 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.

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)
, 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)
, 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)
    REFERENCES contacts (user_id, contact_id)
, FOREIGN KEY (user_id, host_id)
    REFERENCES hosts (user_id, host_id)
) ;
share|improve this answer
    
On domains table you define contact_id,host_id fields unique. What if we assign two domains to the same contact and the same host, or even without the host since is nullable? –  yannis Sep 9 '13 at 12:19
    
That was mistake.,Removed. –  ypercube Sep 9 '13 at 12:33
add comment

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.