I have a program that needs to store *traceroutes* (among other data).

Here is a diagram that depicts the business scenario:

[![enter image description here](https://i.stack.imgur.com/Y4i6V.png)](https://i.stack.imgur.com/Y4i6V.png)

My use case right now is to store the *traceroutes* using the following tables (with their corresponding columns):

**packet**

```
packet_id
source_ip
destination_ip
packet_length

```

**traceroute**

```
traceroute_id
packet_id
timestamp
path_id

```

**path**

```
path_id
traceroute_id
timestamp

```

**ip**

```
ip_id
ip_address

```

**path\_ip**

```
path_id
ip_id
order_index

```

This creates the need to make several `JOINs` to get back a `traceroute`, but it makes the pieces of the `traceroutes` available for query for other needs.

I am considering having a second `traceroute` table that stores the `path` as a json string of `ip_addresses`. This allows me to recover full `traceroutes` with minimal `JOINs`, but still retain the individual hops (`ip_addresses`) used for other queries.

**My questions are**:

1. Does having this duplicated data make sense?
2. Is there a better approach to this?

**Responses to comments:**

I would add the `traceroute` data from one method, and would populate the tables at the same time. 

The duplicated table (perhaps called `traceroute2`?) would just be used for retrievals, and none of the data would ever be edited or updated. 

I am considering this because I am most interested in speed of data retrieval. I did some benchmarking (sort of crude) and I can get a 2-6X speed improvement on retrieval.

I don't want to prevent `JOINs` for a particular reason, just want to avoid this many. For this operation, I never need the individual hops of a `traceroute`, so I don't see what I would gain by splitting them out when I add it, and then piecing them back together again.