Jump to content

Converting Name based relationships to Serial based.


jtoshkov
 Share

This topic is 4304 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 4304 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.