Personally i've made it a habit to always have a primary key in every table that has data. You never know what changes you might make in the future that may require a relationship to that table so its always better to have something already established IMO.
The only table i do not put a primary key in is if i have a utility table or a table for just globals.
1 of 1 people found this helpful
I agree with Matt that every table should have a primary key and it has nothing to do with relationships that bring this strong conviction. Take your example of an 'ending table' such as Student Grades. Assume a power User with rights accidently deleted 150 student grade records. What do you do? What you do is grab backup and import by matching on keys.
There are many such examples where data retrieval and maintenance requires the record's unique identifier and attempting to add the key after the fact is too late. Keys are cheap and all data tables should have them. No keys, no salvation.
This example is reason enough to have a primary key in every table. An ounce of prevention......
I agree every data table should have a primary key whether you are using it for a relationship or not.
That completely makes sense. I hadn't thought of that.
Okay. I'll go through my tables and create primary keys for each one.
1 of 1 people found this helpful
And remember that creating a new Primary Key field is the first of 2 steps:
- create the primary Key field
- populate all empty primary Key fields in older records that are already in the system.
I found files in inherited systems which had a pKey late in the field creation order, but lacked IDs in some records because step 2 was forgotten when the pKey field was added.
Also remember that every table should have an auto-enter creation and modification date (or I prefer timestamp). When something happens and you need to look through the back-end data, these fields are critical in determining an issue. Between these timestamps, the Account Name listed and your Server logs, you can pretty-much back-track to a specific issue AND be able to correct it because the timestamps help you isolate only those records that were affected.
I've got that in all the tables. The Createdby and CreatedTime auto entries, as well as the modification dates and people have saved me on more than one occasion. I can look back and see who messed things up.
Stephen makes a very good point and can often be overlooked. Making sure ALL record get populated with keys even old ones. Good of you to remind people of this.
A trick I discovered to quickly populate a table if you are using regular serials (which I prefer):
- Make note of your total record count in the table. All records do not need to be showing
- Go to field definition and create your ID
- Set it as a type CALCULATION first = Get ( RecordNumber )
- Do NOT make it unstored -
- Exit out of definitions
- Go back in and change it to auto-enter serial (ADDED: and be sure to increment to the next number)
It ignores the sort order of existing records and it is faster than anything else I have timed, surely because it is not iterating through records. And back up first and do NOT do this while served. But I change grouped data this way when normalizing data during migrations in many ways using different calculations. Stored calculations plant the data as regular data.
Unfortunately I can't take it off the server right now. My system won't work as a local copy. It gives me the dreaded "The Access Privilages have been tampered . . . " And it won't work. I've contacted a few outside companies but right now it's not working offline. It only works online.
I'll probably end up using a server-side script to give each record a Primary Key.
I'm sending the file to FileMaker soon to see if they can work on it and fix it. Otherwise I get to rebuild it.
As your attorney, I advise you to use UUIDs instead of serial numbers.
LaRetta, this is an excellent way of populating fields, but why do you reccomend not doing this while the file is served?
The reasons that *I* do not work in schema while served (other than to quickly open and review graph or fields):
- Tests have shown that you can stop new-record creation (if in field definition) and script would fire on existing record, incorrectly changing data. Some call it negligible risk but client data is not up for risk in my book. What would happen if you change a calculation which affected privileges in the middle of a script which then must change its perspective (thus path) partway through?
- When changing field definition or relationship, and if connection is dropped, that is when it will toast your file more likely than any other time. I will only run data-corrections locally for much the same reason.
- If a User is locking a record and you change schema which affects the data, will it hang you in calculation until record free? Or will it hold up Users? They are hung up or you are hung up ... both bad.
- Schema changes are, many-times, experienced by the User as blips, shakes, blank screens and hesitations. Put 20 Users in a room with you and go to town on served files and watch their expressions (you will be shocked at how much they will notice). Each time we unsettle the User, we shake User faith in the system and that is the biggest penalty of all.
- Can we really predict all possible configurations and their outcome, with every calculation and its dependencies and every script being ran in the instant, with various permissions and record-locks? Because it is unpredictable, it is unsafe.
With just the IDs, the risk isn't as great but in principle you are still setting records and you can't test if all of them set correctly or were locked by User at the time. In the time you finish and then check the records to find any which didn't create a serial, a User could have created a child off that record without an ID if 'Allow Creation' as on and they enter data in a child field. Break.
Hey, David, I think I have come up with a new curse ... my prior favorite curse was Chinese which is "May you live in interesting times." But I think I'll change it to "May you change your schema across WAN!!"
I admit to being fanatical about protecting data but really, we are all HERE because of the importance of client data and to put that data secondary to developer convenience is just wrong in my book. I consider you to be a better developer so I am curious why you asked. If you wish to scold me for being too tight then it won't be the first time and I don't mind and convincing arguments might even loosen me up a bit (although I doubt it).