Selecting key fields for relationships

[bluearrow]

Hi,

Is there any standard or recommended best practice for linking two tables ocurrences, assuming you are not creating the related records using a portal, but a script? (the key field is not automatically entered by FileMaker while creating the record).

For instante, in the left side of the relationship:

- should I use the parent table primary key field and link it to same field (foreign key) in the child table (on the right side of the relationship)?

- or should I use the parent table foreign key field linked to same field (parent key) in the child table?

- or is it just a matter of tastes and needs?

You see, in a Quoting system where you have Quote/Project/Invoice, I could link them like:

k1_parentquote (QUOTE) > k2_foreignquote (PROJECT)

but also

k2_foreignproject (QUOTE) > k1_parentproject (PROJECT)

and so on...

Because I am scripting record creation, I copy key fields from one table to another using variables. Thus, no special reasons for me to use one or another. I could even create both relationships.

What is your opinion about it? Is there something I should avoid at all? I was wondering if you follow any rules in here or there are not any rules at all.

Thanks in advance for your comments

Edited November 23, 2007 by Guest

[Fitch]

Link the primary key of the parent record to the foreign key of the child record. That is what defines a parent/child relationship. Which table is the parent is up to you: is "quote" a line item of a "project," or is project a line item of a quote?

[bluearrow]

...is "quote" a line item of a "project," or is project a line item of a quote?

Neither one really. It is a one to one relationship. The same goes for Invoices. So the link is:

One quote > One project > One invoice

But I may link them one way or another. I could then script field lookups and autoenters accordingly. I am just checking how other people generally deals with this. Of course there will always be a parent table and a child table, but which one should always be the parent? I am sure of the answer in a one to many relationship, but what about a one to one? or is this not relevant at all?

[comment]

If projects are going to be created as a result of quotes being accepted, then it might be more convenient to make project a child of quote. But it really matters very little in a one-to-one.

[bluearrow]

Thank you,

Your reply clarified my point.

Regard