🚀 go-pugleaf

Hi

This is a problem that I'm having and I was wondering that maybe you guys
could come up with some ideas.
Ok, let's see if I can explain the problem.

I have about 15 tables. Each table has several fields, however each table
has a common field called 'Code'

ex.

Table Vehicles
--------------
Code
Make
Color

Table People
-------------
Code
Name
Age

Table Addresses
-----------------
Code
City
ZIP
...

Now we want to relate records from one table to another.  For example, we
want to say 'John has two cars'
In order to do that, I have an external table called Links with source an
destination fields. For the example we would have

Source    Dest
----------------
P132      V100
P132      V242

(P132 is a record from the People table with code 132, V100 a vehicle from
the Vehicle table with code 100,  and V242 a vehicle
with code 242)

So, with all that, we know that John (P132) is related with two vehicles. If
I want to run the query 'People associated with vehicles'
it would be easy to query that table and get that information.

Now, let's suppose we have this situation:

Source    Dest
----------------
P132      V100
P132      V242
P140      V500
P132      P140

In this example, John is related with Michael (P140) and Michael is related
with a vehicle (V500).
If I want to run the same query than before 'People associated with
vehicles', because of P132 is related with P140 and P140 is related with
a V500 this implies that P132 is related with V500.

Now, the query is not so simple, because it's like a graph and I need to
make a Depth First Search to get what I want. Doing that, I will get that
P132 is
related with V500 because there is a 'path' to get there.

I have done all this and it works fine. The problem that I have is that I
need to make that Depth First Search for each people that I have in the
database
in order to see if I have vehicles associated with it. If I don't have many
records it's ok, but If I have 100,000 people on the database I would have
to
make that search 100,000 times just to see if there is a path between them,
I mean, if there is a way to get from a person to a vehicle.
In this case... this solution doesn't work, because it takes forever...


What do you guys suggest I can try? I though that one solution would be to
have a process running on the server that maintains a table with ALL the
links that we have in the database and the depth of the link. So, in our
example we'd have a entry "P132 V500 with depth 2"
Having that table it would be easy to query. Inconvenience: the table is
going to be HUGE, and also the process has to be smart enough to keep track
of
deleted links, new links...

I'd appreciate if you guys have any idea or suggestions about this and how I
can get a better perfomance.

Thanks a lot for reading this whole message!!