Converting Name based relationships to Serial based.

[jtoshkov]

Hi Everyone,

I have to update an old, rather large database with multiple relationships based on Names. I want to do it right and use Unique serials for the relationships. Can someone recommend some best practices for converting such relationships.

Thank you.

[LaRetta]

Back up first. Outline: Create your primary keys in all your tables first. Then use your existing relationship to place those new primary keys in your child tables. Here is one example using Customers and Invoices:

In Customers, create a unique, auto-enter serial number in field definitions, such as CustomerID. Then show all records and Replace Field Contents [replace with serial numbers; starting at 1 and increment by 1]. Be sure to check the box which says 'Update serial in auto entry options.'

Go to your child table and create CustomerID (number). Show all records then run Replace Field Contents [by calculation and select Customers::CustomerID]. This is using your original relationship to grab the new CustomerID values.

Once all tables hold their primary and foreign keys, change the relationships as Customers::Name = Invoices::CustomerName to Customers::CustomerID = Invoices::CustomerID

Developers differ on naming conventions. If the ID is the primary key some start it with pk (for primary key) and start it with fk if it is in a related table (foreign key). I am more old-fashioned. If CustomerID is in the Customers table, I know it is the primary key and if it is in the Invoices table, I know it is the foreign key. I find it cleaner to write and read without the pk/fk designations, but that's just me.

UPDATE: When running this process, be sure no Users are the system so you won't have to worry about record locking.

[jtoshkov]

Back up first. Outline: Create your primary keys in all your tables first. Then use your existing relationship to place those new primary keys in your child tables. Here is one example using Customers and Invoices:

In Customers, create a unique, auto-enter serial number in field definitions, such as CustomerID. Then show all records and Replace Field Contents [replace with serial numbers; starting at 1 and increment by 1]

Go to your child table and create CustomerID (number). Show all records then run Replace Field Contents [by calculation and select Customers::CustomerID]. This is using your original relationship to grab the new CustomerID values.

Once all tables hold their primary and foreign keys, change the relationships as Customers::Name = Invoices::CustomerName to Customers::CustomerID = Invoices::CustomerID

Developers differ on naming conventions. If the ID is the primary key some start it with pk (for primary key) and start it with fk if it is in a related table (foreign key). I am more old-fashioned. If CustomerID is in the Customers table, I know it is the primary key and if it is in the Invoices table, I know it is the foreign key. I find it cleaner to write and read without the pk/fk designations, but that's just me.

UPDATE: When running this process, be sure no Users are the system so you won't have to worry about record locking.

That is exactly what I was looking for. I know how to set my tables and relationships starting from scratch, but I wasn't sure if a Lookup or Auto-enter or Replace Field Contents with a calculated value would be best in this case.

Thank you.

[LaRetta]

I neglected to mention (although I'm sure you know this) ... when serializing your primary keys using Replace Field Contents, be sure to check the box which says 'Update serial in auto entry options.' I corrected the above post.