I have been reading about using key fields properly and rules for good database design. Problem is I am converting an old v4.5 app to v8 (via 5 & 7) and fear I have not stuck to my own rules. The idea was to have an autoincrement primary key and a user friendly natural key which could be edited if needed without corrupting any links in sets based on the primary. The natural key could then be used for lookups and such.
Ex: Dircode - D0031 = Dir_ID - LORBER
So I am guessing that the natural key should not exist in any of the other tables than the one in which it was originally designed - else when it is edited it will not find the right records if used for searching the other tables. So anywhere else I want to use the natural key, it will have to be provided by a calculation, assuming the primary key and foreign keys match. I use Record-List combo boxes quite a bit on my forms for searching and locating records so this should not be too hard.
Ex: To find a Knit Order the user types in the known Cust P/O No. to find the unknown Knit Order No. If the Cust P/O No. is edited in the Knit Order, the record stays linked because the Knit Order No. never changes.
What I have done however is added the natural key to the other tables and now need to undo this. I am thinking that I ought to go thru my forms & reports and where ever I have a natural key displayed that is not from the primary table, change it to a lookup() calculation before removing the field from the other tables.
I was wondering if anyone has any suggestions that I might want to consider before I get into this project.
My 2nd question is whether adding multiple foreign keys is a good idea or not. For example, I need to get a count from a table that otherwise does not need the Knit Order No. If I add it to this table then my data entry for those records gets more complicated, but getting this value is much easier. Is that a good basis for how to decide if it is worth doing?
I'm trying to keep it simple but this app won't let me!
Edit:
Perhaps in those tables where I added the natural key I could change the field rule to make it a calculated field to lookup in the primary table so it is always current. Quickbooks does something like this because you can change what appears to be a primary key and have it cascade the change everywhere it appears. However the user is able to type in that value, which you cannot do in Alpha if the field has a calc field rule.
Do I sound confused?
Ex: Dircode - D0031 = Dir_ID - LORBER
So I am guessing that the natural key should not exist in any of the other tables than the one in which it was originally designed - else when it is edited it will not find the right records if used for searching the other tables. So anywhere else I want to use the natural key, it will have to be provided by a calculation, assuming the primary key and foreign keys match. I use Record-List combo boxes quite a bit on my forms for searching and locating records so this should not be too hard.
Ex: To find a Knit Order the user types in the known Cust P/O No. to find the unknown Knit Order No. If the Cust P/O No. is edited in the Knit Order, the record stays linked because the Knit Order No. never changes.
What I have done however is added the natural key to the other tables and now need to undo this. I am thinking that I ought to go thru my forms & reports and where ever I have a natural key displayed that is not from the primary table, change it to a lookup() calculation before removing the field from the other tables.
I was wondering if anyone has any suggestions that I might want to consider before I get into this project.
My 2nd question is whether adding multiple foreign keys is a good idea or not. For example, I need to get a count from a table that otherwise does not need the Knit Order No. If I add it to this table then my data entry for those records gets more complicated, but getting this value is much easier. Is that a good basis for how to decide if it is worth doing?
I'm trying to keep it simple but this app won't let me!
Edit:
Perhaps in those tables where I added the natural key I could change the field rule to make it a calculated field to lookup in the primary table so it is always current. Quickbooks does something like this because you can change what appears to be a primary key and have it cascade the change everywhere it appears. However the user is able to type in that value, which you cannot do in Alpha if the field has a calc field rule.
Do I sound confused?
Comment