captkurt: why no serial #s?

[Yossarian]

Greetings,

I am a newcomer to the forum and have only been using filemaker for about a year (though long enough to convince the company I work for that I am much smarter than I am - I love this product...) but I saw a post of yours from several months ago where you emphatically stated it was terrible to use sequential serial numbers in ID codes in filemaker. I have been using this frequently and would like to know why you feel this way.

Thanks for any input...

[Kurt Knippel]

quote:

---

Originally posted by Yossarian:

Greetings,

I am a newcomer to the forum and have only been using filemaker for about a year (though long enough to convince the company I work for that I am much smarter than I am - I love this product...) but I saw a post of yours from several months ago where you emphatically stated it was terrible to use sequential serial numbers in ID codes in filemaker. I have been using this frequently and would like to know why you feel this way.

---

Is "1", "2", "3", etc a unique series of record IDs? Nope!

Is "1", "2", "3", easy to duplicate? Yes!

Does a multi-key made up of "1", "2", "3", etc have any chance of creating a meaningful relationship? Nope!

Does any other RDBMS in existance allow the use of simple sequential serial numbers as IDs? NOPE!

Just because Filemaker allows such a thing does not mean that sloppy planning and development is good.

It is no more difficult to create a truely unique ID for each record. I use Status ( CurrentRecordID ) & "-" & Status ( CurrentTime ) & "-" & Status ( CurrentDate ) as an auto-entered calculation into a Text field. This creates a virtually unduplicatable ID, unique to each record.

I cannot even begin to explain the number of times and ways that I run into this situation and it is causing some kind of problem.

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Senior Filemaker Developer

http://www.database-resources.com

mailto:[email protected]

=-=-=-=-=-=-=-=-=-=-=-=-=

[daverk]

hmmm, actually i use automatically generated sequential record ids all the time in Access and haven't had a bit of trouble with them. they work fine as unique ids and to link records. if i have a one to many relationship and need a unique multi-field key in the 'many' table i can join them with some other field to create a unique key.

you say they don't work but i'm not sure why.

[Kurt Knippel]

quote:

---

Originally posted by daverk:

hmmm, actually i use automatically generated sequential record ids all the time in Access and haven't had a bit of trouble with them. they work fine as unique ids and to link records. if i have a one to many relationship and need a unique multi-field key in the 'many' table i can join them with some other field to create a unique key.

you say they don't work but i'm not sure why.

---

I cannot speak for certain, but I think that Access uses a record ID that is behind the scenes and is NOT seen by the user. The number you are refering to is another ID that you can use.

However GOOD RELATIONAL DATABASE DESIGN should not be done this way. Access is not really a good RDBMS and has some of the same issues as FMP in this regard.

Let me reiterate:

Is "1", "2", "3", etc a unique series of record IDs? Nope!

Is "1", "2", "3", easy to duplicate? Yes!

Does a multi-key made up of "1", "2", "3", etc have any chance of creating a meaningful relationship? Nope!

Simple sequential serial numbers (1,2,3...237,238,etc) do NOT uniquely identify records and should not be used as such.

Just because you may never have broken a bone, does not mean that your bones are unbreakable!

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Senior Filemaker Developer

http://www.database-resources.com

mailto:[email protected]

=-=-=-=-=-=-=-=-=-=-=-=-=

[Yossarian]

Hmmmm, I see your point. I do think I will still use these serial #'s because they come in handy mostly for letting the user quilckly find a record based on a 6 digit easy to recignize id# ( Status ( CurrentRecordID) & "-" & Status ( CurrentTime ) & "-" & Status ( CurrentDate ) is a little cumbersome to type in a "find" query), but I have been frequently using Lastname&id#&vendorname for relational field. Your currenttime and date suggestion could prove useful.

Thanks for the tip!

[Kurt Knippel]

quote:

---

Originally posted by Yossarian:

Hmmmm, I see your point. I do think I will still use these serial #'s because they come in handy mostly for letting the user quilckly find a record based on a 6 digit easy to recignize id# ( Status ( CurrentRecordID) & "-" & Status ( CurrentTime ) & "-" & Status ( CurrentDate ) is a little cumbersome to type in a "find" query), but I have been frequently using Lastname&id#&vendorname for relational field. Your currenttime and date suggestion could prove useful.

---

You NEVER let the user access the ID! NEVER, EVER!

You, as the developer, use the ID to do the relationships and is used for much of your functionality.

The ID is NOT a user field. Just like you do not use a user-enterable field for your IDs, you do not let the user use your ID field.

All it takes is for one user to change one of those IDs and orphan a bunch of financial data for you to realize why I say this stuff.

Being an easy solution is not an acceptable reason for being sloppy, and is simply not a good business practice.

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Senior Filemaker Developer

http://www.database-resources.com

mailto:[email protected]

=-=-=-=-=-=-=-=-=-=-=-=-=

[Yossarian]

whew! My ears are burning...

Let me defend myself,

The "ID#" is actually the same as the invoice#, and is auto entered at the time of creation, and unmodifiable (sp?) (strict, do not let user override). I do not see how letting users use this # as a way of identifying and locating records could in anyway endanger the information. I do understand and agree with your emphatic admonishment of any practice where the user can accidently alter this #, but I do not believe that is the case in this particular example

[daverk]

don't mean to be a pain but why aren't 1, 2, 3, etc. unique? i mean 1 doesn't equal 2 and never will. i recognize that they are 'simple' but why aren't they unique. as long as the number can't be used in more than one record. btw i never let my users see the id's or access them. you're absolutely right about that.

actually, i would much prefer not letting the users use the app at all. unfortunately i can't convince my boss of the utility of that. but just think. if they never use it, you'll never have bugs or glitches or foul ups. sigh......

[Yossarian]

let me see if I can handle this one...

I believe the concept is that while "1,2,3..." may be unique for ONE file, when you start working with relational files, this is no longer true (it is not that hard to concieve of a 77911 occuring in both "customerid#" and "productid#, if both are using sequential serial #s)

[HarryDP]

But you wouldn't create a relationship between

"customerid#" and "productid#" and if you were performing a search on an ID# it would be performed on a particular field. But as many have already stated the end user shouldn't have access to these "system" numbers.

Self generating ID#s are reliable. Just because it is simple doesn't make it unreliable.

[Ocean West]

In the case of using client dictated numbers as keys in a relationship has been in my opinion disasterous. In most cases the find some where down the line that they need to modify it or do something with it. Or choose to contain the entire DNA sequence of a job as it were in the number key. It is just not good practice. That is my opinion.And one that I only developed when someone sat me down and expalined it rather bluntly. I was convinced that my way would work. But I was wrong.

And YES in many cases it it does work fine and there is nothing WRONG with doing that way. But I emplore all aspiring FMP Developers to thinking of your project as you see it a year from now or longer. It will save time as hastel in the future.

I always think of an Alias a behind the scenes duplicate of the field, that incriments how ever you want by using, random numbers combined with date and time, or serial numbers or whatever. Just pick a method that is unique to the application you are developing and then go from there. This way if the usere decides to modify the "For Show" number they will not break any links or orphan any children records.

With multi-key'd relations that may be more curcial to control or make for certain you have uniqueness with that in mind you should explore all the different techniques that are published for creating true unique serial numbers.

What I do is create a number generator database that I interigate and update when I need to create a new record. I control all the record numbers when I need them via scripts, Because some times when you create a new record you don't want to assign a new number immediately, if you do error checking and other data integreity checks.

I hope this helps.

[mikehkg]

The CurrentRecordID as a reference to records in other files is risky because it does not survive any importing; e.g. if you need to move your database into another file, the CurrentRecordIDs will not be exported and therefore all Related Records may mess up if you had previously deleted only one entry in the old database.

In my solutions, each file has a generated Auto-Enter Serial Number for each record which is not user-changable but also importable. In addition there's a userfriendly version of the number, for example "123.443" for the user to see and search for.

I use the CurrentRecordID only for navigational use, e.g. to go back to that previously seen record

after jumping to another, in a list for example, and in more complex scripts.

[Kurt Knippel]

To add to this discussion let me restate the following: Serial Numbers are not inherantly bad...they are just bad as KEY FIELDS. I use a serial number in every one of my tables in all my solutions, they are useful for any number of reasons.

I just DO NOT use them for the primary relationships between records for the following reasons:

They are easy to break or get out of sequence - this can happen with a Replace, with Set Next Serial Value script step, by reseting the value in Define Fields, by importing data from another file, etc.

They are subject to interpretation by the user and thus subject to change. Business rules change and the company that was COM-001 is now COMP-101, so the user changes it. All relationships now broke, but the user does not realize this and goes on thier merry way. 2 Months later you are call when all hell has broken loose. I have been called into a couple of companies to try and fix this exact problem, one did not save any record of the old Company numbers.

They are easy to hack - one could through purpose or accident reset them to a different sequence and still maintain the appearance that everything is OK. COM-001 gets changed to COM-121, still a valid number and has records in it...it would take a ******* sharp eye to realize that this has happened, since most often everything will look correct.

How many times have you seen users entering data in Find mode or worse entering find criteria in Browse mode...thus replacing those serial numbers with something different..directly leading to the above situation.

Going with an auto-entered scheme that the user NEVER interacts with, prevents all this from happening. Keep the user modifiable serial number COM-001, but DO NOT use this for you primary relationships...use a hidden auto-entered number.

My scheme is nice, because it is non-sequential and non-random, so the chance of accidental duplication or reassignment is almost zero. It really take deliberate effort to break it. It also works with imports and exports, in remote user (i.e. laptop) users or other types of synchronized solutions. And even if a user ever sees it, it means nothing to them, so they are not inclined to change it.

Does that help?