Valentina Digest, Vol 39, Issue 21

[Ruslan Zasukhin]

On 3/24/06 8:05 AM, "Robert Sneidar" <bobs at twft.com> wrote:

Hi Robert,

> Thanks for the response.
> 
> Upon reading up on everything, it appears I will end up using both
> local AND server methods. The app I am porting uses local temporary
> tables that are destroyed upon exit from the app or the module in
> use. I don't like the "dirty" feeling I get creating and destroying
> temporary tables on a full blown server. Those database structures
> should be relatively fixed imho.
 
> On another note, since I am porting from Foxpro, it seems that
> foreign key links are the most similar to Foxpro relations.

Yes, this is the same thing

> But in  
> the app I am porting, they do not use database (collections of
> tables, indexes, etc) as I will be doing, but instead, they have
> individual tables on the disk.

You can think about all these tables as single database.

> So I have 3 questions, and I think I
> know the answer to the first one.

> 1. Occasionally I may want to create a relation between 2 tables in
> different databases. Is this possible? (Probably not).

Not possible 
 
> 2. The Foxpro app creates and deletes links (called relations) on the
> fly as it needs them. If I do that with Valentina using very large
> data sets, is there going to be a performance penalty hit?

No, just make sure that fields are indexed.

> Does  creating a link take time to build? I am talking about potentially
> databases of 800,000+ records in a 1 to many situation.

Look. When you specify FOREIGN KEY in the your database structure, you get
something which live long time, which is persistent.

Usually we define FOREIGN KEY, when we want to have something that will
control referential integrity. (CASCADE, SET NULL)

IF you do not need this, you can NOT define FK.
To get "FoxPro links on the fly" you simply do:

    SELECT
    FROM T1 join T2 on T1.f2 = T2.f4

Valentina internally really will establish "link on the fly",
And make join using this link.
    
So everything like in FoxPro here.

> 3. Finally (and I thank all of you for your patience in reading thus
> far) In a foreign key link, does the pointer in the child table move
> when the parent record changes?

YOU SELF can specify this!  :-)

Using ON DELETE and ON UPDATE settings of FOREIGN KEY

> And if so, is there a function for
> retrieving just the records that match in the existing link?

It seems you simply asks about JOIN.

> That is,  
> if the parent is an invoice header record, can I retrieve all the
> child records with an sql function?

Of course, this is simple join:

    SELECT
    FROM Invoice join child on Invoice.id = child.InvoicePtr

-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]