I use longings as relational keys because it is easier to track down when the user(s) come to me as ask why some related(?) data is not where it should be. [table]ID = 1234 is a lot easier to work with then [table]ID = B83F47092A384821A9FABA5379C3CCC0 when try to track data.
I don't use relational keys for user data. Requisition Numbers, Sample ID, etc -- BUT -- these values need to be created too. In some instances either the user, or the usage, dictate a 'non-broken' sequence of values. This discussion can help to design a mechanism to fill that need. In my systems the users can define their own sequence. They can setup the length, whether it is purely numeric (1,2,3,4 ..) or alphanumeric like most state's license plates (ABC123, ABC124 etc), include assignment date as part of the sequence value (ABC08-28-18), include other fixed text in the sequence (ABC-123,ABC-124), as well as indicate whether the sequence is to be 'unbroken'. At some places, it is necessary to make an assignment of these (user defined) IDs before other records are created, not because the other records are (directly) dependent on the ID, but because ID order is important. scenario mom and her offspring are being entered into the system. Both use the same user created sequence for their respective, user visible, IDs. Also each need an internal relational ID. Mom - needs to have the user created/visible ID generated before the offspring, so that while the IDs do not need to be sequential (1,2,3) they do need to be ordinal (1, 5,6,7,8). The offspring, in this system, are created and displayed through a secondary process spawned from the Mother's entry process. So... mom gets a relational ID, and a user defined ID on record creation, so that offspring are properly IDed. On Tue, 28 Aug 2018 16:29:54 +0000, Benedict, Tom via 4D_Tech wrote: > Kirk Brooks writes: > >> I am surprised this thread has drawn so much discussion. It's good >> discussion but scanning back over it a key point is that all of these >> contortions and workarounds to preserve a number sequence are based on the >> single design decision to use an invoice (in this case) number as a key >> field. I'm not criticizing of course - I used to do this as well and still >> have a few cases where that sort of design exists though nothing involving >> number series where gaps are an issue. Not to mention I've been doing 4D >> long enough to recall the first discussions about data normalization the >> community had and the, erm, hesitant embrace of it by many. > > Thank you Kirk for finally mentioning this. The use of 'magic > numbers' is tempting, but ultimately leads to lots of work to > support. Keys should have no meaning. > > That said, the need to manage sequence numbers without gaps is > legitimate and an interesting subject (as this thread has > illustrated). Just don't use them as relational keys, or if you do, > make sure you do it with your eyes open. > > Tom Benedict > Optum > > > This e-mail, including attachments, may include confidential and/or > proprietary information, and may be used only by the person or entity > to which it is addressed. If the reader of this e-mail is not the intended > recipient or his or her authorized agent, the reader is hereby notified > that any dissemination, distribution or copying of this e-mail is > prohibited. If you have received this e-mail in error, please notify the > sender by replying to this message and delete this e-mail immediately. > ********************************************************************** > 4D Internet Users Group (4D iNUG) > Archive: http://lists.4d.com/archives.html > Options: https://lists.4d.com/mailman/options/4d_tech > Unsub: mailto:[email protected] > ********************************************************************** --------------- Gas is for washing parts Alcohol is for drinkin' Nitromethane is for racing ********************************************************************** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:[email protected] **********************************************************************
