🚀 go-pugleaf

just my suggestion :-

you need to modify your database design to ensure
your data accessing will be efficient.

the entity relationship
between PEOPLE and VEHICLE
is ONE-TO-MANY or maybe MANY-TO-MANY.

explaination; one PEOPLE can have many VEHICLE.
normally, one VEHICLE can only be own by one PEOPLE.
but, on your condition, if one PEOPLE related on another PEOPLE
and as i'm understood, the VEHICLE related on that another PEOPLE will be
own by that one PEOPLE too.

my suggestion is; you make an inter-section table which combining the code
from
VEHICLE and PEOPLE. you can called it PEOPLE_VEHICLE.

in table VEHICLE (Code,Make,Color,...)
in table PEOPLE(Code,Name,Age,...)
in table PEOPLE_VEHICLE(CodePeople,CodeVehicle,Status)

in field Status at table PEOPLE_VEHICLE; you can set option
if the connection between PEOPLE and VEHICLE, then set PV.
but, if the connection between PEOPLE and PEOPLE, then set PP.

for example,

PEOPLE
Code     Name        Age
132       Alex           30
140       George       27

VEHICLE
Code      Make        Color
100        US             Blue
242        US             Black
500        US             Black

PEOPLE_VEHICLE
CodePeople    CodeVehicle     Status
132                 100                   PV
132                 242                   PV
132                 140                   PP
140                 500                   PV

if you do this method, you can only using this inter-section table to
access/link on each record related between PEOPLE and VEHICLE table.

than, you just query such as like this;

select * from PEOPLE_VEHICLE
where (CodePeople='132' and Status='PV') or (CodeVehicle='132' and
Status='PP')

that's all. hope it'll help you.
from my experience, the technique of search depend on your database design.
if you make normalization for your database design, you'll get the best
entity-relationship for your tables.



"Star" <lem@lemforeverREMOVE_THIS.com> wrote in message
news:40439ba3$1@newsgroups.borland.com...
> 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!!
>
>
>
>
>
>
>