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 a question about Relational Database design. I'm just learning the basics of databases, so bare with me, in that I'm not sure of the technical terms for some of the concepts I'm trying to express here.

In short:

In the database structure in the image below, there is no replicated data in different tables, so far as I can tell, this design is 3NF compliant, however maybe I have misunderstood one or more of those concepts.

I'm unsure as to whether or not having multiple relationships in the design creates possible issues with referential integrity. A picture says a thousand words at this point: http://img580.imageshack.us/img580/3757/t9ku.png

If you look at the Service table in the middle, it has a relationship directly to Customer. It also has a relationship to Site, which in turn has a relationship to Customer. Is this okay, or should I remove some foreign keys so that the only 'path' from Service to Customer, is via the Site table?

In long:

I'm working on a practice example revolving around data services. I've created my 'structure', that is to say the tables themselves and the key's in them (both primary and foreign) in order to see the relationships. What has struck me is that it is possible to define a few of my tables to have more than one relationship to other tables, such that there is more than one 'path' between tables.

Does this create possible issues of referential integrity? (i.e. You can get from one table to another, with or without going 'through' other tables, the extra relationship appears to allow some tables to be 'bypassed')

Is this a bad thing in a relational database? Should any given table only have a single relationship to another table such that the 'path' through the database is 'linear' rather than having choices in the 'path'? Or, assuming this is in fact 3NF compliant, is being able to define more relationships a good thing? (i.e. it might make query design easier in some instances because the direct relationships allow you to avoid having to join lots of intermediate tables...)

share|improve this question
add comment

migrated from serverfault.com Oct 16 '13 at 21:38

This question came from our site for professional system and network administrators.

1 Answer

In your model, every Service has one Site and every Site has one Customer. Now you have to ask yourself: Is there a use case where the CustomerID associated with the service can be different from the customerID associated with its site?

If a service can have a service-customer and a site-customer, then this design would be correct. If not, you have stored redundant information in your database by including the CustomerID column in the Service table and redundancies should be avoided in good database design.

share|improve this answer
    
Okay, yep I see the point now. In my case no, a customer does not a have a direct relationship to a service. The customer is related to the site, the site is related to the service. The relationship between customer and service is only an indirect relationship therefore. I'm not sure why I didn't think of it that way before! Thanks very much for the clarification. –  Chris Oct 17 '13 at 21:57
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.