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

Now:

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.

Answers


Well, identifying relationships and "more natural" keys can lower the need for JOINing.

For example:

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>

Need Your Help

How to run Behat with Internet Explorer?

internet-explorer symfony behat mink

I have spent a lot of time searching for the solution and it seems like it should be possible however I have been unable to find a way so far.

Wait for element to load when testing an iOS app using Appium and Ruby?

ios ruby appium

I am testing an iOS app, and can't interact with the elements after logging in because Appium is going too fast.