Paul,

>I am wondering if I could get some of your opinions on an issue that I have struggled with for quite some time. I seem to go back and forth on this issue.
>
>I am using Visual Maxframe Professional and I am using the routines built in it for creating pk's for each table. Along with the pk's I generally have another unique ID.
>
>Lets take a simple example...a customer file has both a pk field and an acctno field. I also have an invoice table with both a pk field and an invoice# field.
>
>For each invoice there are multiple line item records. What I am curious about is whether I should put the invoice pk or the invoice# in the sold line item table and also if I should put the customer pk or the customer acctno field into the invoice table.
>
>I know this is a pretty stupid question but since I have struggled with this quite a few times with other programmers I thought I would try to get anyone else's opinion.

I've discussed this matter with Jim Booth a while ago. There is alot to say for both. IMO, you've got to make a decission to wich method you want to use. I'll sum up the most evident pro's and con's:

- If using generated PK with an integer field, this will likely the most efficient way to keep your indexes small as the cost are only 4 bytes, keeping up performance to the optimum.
- Also, you won't have to deal with the situation where a user (wherther accedently or not) adds, delete and again want to add the same acctno value (Then you've got to have the primary field filtered on the DELETED() function, which is not optimizable so you'll need another regular index on the table)

OTOH:
- when using generated PKs, it will be more difficult to 'read' the child tables because now they have a meaningless number instead of for example a Acctno number making it somewhat more difficult to debug or doing thing under the hood.
- When reporting it's likely to occur that you need more relations (xBase way) or JOINS (in SQL) to collect the other (candidate) key of the parent table for inclusion in the report, decreasing performance.

It's up to you to decide which way to go. I've found that many prominent developers up here tend to use generated PKs. For myself i'm quite comfortable with using 'intelligent' keys. I see benefits to both, but I think you'll need to be consistent. If you choose for one, you should stick with it for the whole app or project.

When using generated PK's it sure is wise to label the intelligent key as a candidate key to prevent multiple instances of the acctno (again you'll have to filter this one on DELETED())

Walter,


>
>Thanks,
>
>Paul Acton