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 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?
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...)