Relational Database Design: How to design relationships with long paths?
I am currently designing a MySQL Database and I am confronted with the following problem I am not sure how solve / design properly.
I have the entities: (simplified)
Providers, Addresses, Letters, Faxes
Adresses belong to providers, Providers have many Addresses Letters belong to Addresses, Addresses have many Letters Faxes belong to Letters, Letter have many Faxes
So now I am working in PHP with an ORM with this Database Model and find myself in the situation that I have a instance of a Fax-Object and what to load the according Provider.
Now this would either cost me a big join or several queries.
The path I would need to go is:
Fax -> Letter -> Address -> Provider
Now I am thinking if I should establish a direct relationship between Faxes and Providers, which would solve this problem. But wouldn't this be redundant and would come at the cost of double effort? What if the relation between a Fax and a Provider changes? Then I would adjust both relationship paths.
What is the preferred way doing this? My example is a little bit simplified. In reality the path I have to go is a little longer.
Well, identifying relationships and "more natural" keys can lower the need for JOINing.
Since there is Faxes.ProviderId, you can directly JOIN Faxes and Providers.
And since InnoDB tables are clustered, you can expect good performance if you ever go in the opposite direction (e.g. "get all faxes of the given provider").
The downside is "fatter" keys and relative unfriendliness towards ORMs. So, I guess this is a matter of compromise, and you are the one who gets to decide which option is better.
What you could do is make the provider-subsequent tables related to each other as weak entities — where part of the identifying primary key of each table is dependent on another table (via a 1:N identifying relationship). You can model this like so:
providers ----------------- provider_id [PK] ... addresses ----------------- provider_id [PK] (FK Referencing providers.provider_id) address_id [PK] ... letters ----------------- provider_id [PK] (FK Referencing addresses.provider_id) address_id [PK] (FK Referencing addresses.address_id) letter_id [PK] ... faxes ----------------- provider_id [PK] (FK Referencing letters.provider_id) address_id [PK] (FK Referencing letters.address_id) letter_id [PK] (FK Referencing letters.letter_id) fax_id [PK] ...
This way, given a particular fax record where you only need to find out more information about the associated provider, you only need to join one table like so:
SELECT subj, content FROM faxes INNER JOIN providers USING (provider_id) WHERE fax_id = <faxid here>