| What is a meaningless number? | | | | should point out that many good programming firms |
| Meaningless numbers are ID and CODE columns which | | | | are based on "Intelligent Design" such as Auto-Mate |
| uniquely identify a record in a database table. They | | | | Software of the USA Please visit this site to read |
| were made up by computer programmers, and in | | | | about the story about "Enter the field matrix |
| traditional programming, they formed the first entry | | | | number".... It's a great story. |
| window where you were responsible for | | | | Let's go a step further and call a byte a byte. |
| remembering that "D" means Doctor, and "402" is | | | | Whether it's a field matrix number or a part number, |
| your best customer. Since it would be wrong to | | | | due concern needs to be put into the design of a |
| discriminate against an entry window just because it's | | | | "key field" lookup to determine whether or not it's |
| a letter and not a number [age of equality] I'll include | | | | meaningful or meaningless. On a part number, why |
| meaningless letter combo's in this theory also. | | | | not both? To programmers, here's my argument: |
| The point? | | | | why not use this technique so that if the part |
| Users should not be required to remember "402" for | | | | number is mis-entered, or changes, you can simply |
| General Electric of Massachusetts and "791" for | | | | reenter the part number without altering the |
| General Electric Turbine Division", but programmers | | | | relationship? Madness or magnificent? It's your opinion |
| should indeed still make use of them as the basis of | | | | that counts. |
| relationships between database table records. Just | | | | Let's take an example in RoloFlex 12.3 from Applause |
| don't expect the users to enter or remember them: | | | | Software (open source and freeware). The database |
| the truly meaningless numbers and codes can be | | | | tables all have a unique ID that is 10 digit numeric: |
| totally hidden from users and as a study, I will refer | | | | Please refer to the running program and you will see |
| to RoloFlex by Applause Software as a good | | | | that none are visible. In addition, I have coded the |
| example where each table is based on a unique | | | | key fields as "noenter" so that if they were made |
| meaningless number. [reference: free RoloFlex] | | | | visible, they could not be used! Ten digit ID numbers |
| What is a meaningful number? | | | | for everything? Meaningless or Madness? |
| A meaningful number is those database records that | | | | Hands again? Each programmer who has had to |
| are referred to commonly by number such as PO | | | | increase a user's key ID field from 6 to 8 to |
| #4067, Sales Order #1, and Speeding Ticket | | | | accommodate 20 years of data entry please stand |
| #352-4635A. These numbers exist on paper and are | | | | up! I'll bet there are a good many among us. Let's |
| the primary way of referring to a record of this | | | | also note in passing that dates on computers are |
| type. When you go to look up my speeding ticket, | | | | stored as 6 digit integers, and in the western world |
| you might indeed look it up under my name also, but | | | | we are almost out of numbers in the six digit length! |
| when a payment is received [good luck] you want to | | | | Why have we not expanded our dates to 8 or 10 |
| be able to enter the printed number 352-4635A and | | | | digits? I guess we'll have to wait until "Y6K" to find |
| quickly locate and apply payment before my check | | | | out! |
| bounces. | | | | The design of meaningless numbers in action: |
| Can a meaningful and meaningless number co-exist? | | | | Let's take on a RDBMS [relational database |
| Yes. Numeric meaningless numbers are the foundation | | | | management system] example and specifically look at |
| of unique identifiers of each record, and a meaningful | | | | parent and child table design. My first point should be |
| number can co-exist as [both] uniquely indexed | | | | obvious to most programmers, that a parent ID field |
| [findable] columns in the same data table. | | | | is used to uniquely identify the parent but (being |
| There's a space here while the programmers hold | | | | meaningless) is not displayed to the user at all. In |
| their heads. | | | | RoloFlex, please note that the parent database tables |
| Let's say you are a database programmer and know | | | | of the main table have a unique ID which drops down |
| that numbers rather than a string which varies in | | | | into the main table upon a save, forming the |
| length [throwing off the sort order completely] | | | | relationship without being seen. |
| should be the foundation of the database record [for | | | | Please note (when inspecting the inner table def's for |
| speed and relationship purposes only], you can | | | | RoloFlex tables) that the Key ID column has a |
| program your unique (meaningless) ID column of | | | | noenter attribute, meaning that the user cannot |
| speeding tickets, and also have a unique (meaningful) | | | | change or type in a new value to that column should |
| column which is ascii [mixed numbers and letters] | | | | it appear on the screen. It is auto-incremented from |
| which is used for finding it. | | | | the system file "sysfile" and is totally meaningless to |
| Why not make the key column a automatic | | | | even the database designer other than being a |
| increment number for relation purposes alongside a | | | | unique value. No surprises here, but let's look at a |
| humanly enterable column consisting of alpha-numeric | | | | child table to see the same technique: |
| characters? Hands please? How many programmers | | | | So, what's the surprise here? On many occasions, I |
| have written 200 lines of code to auto-increment | | | | have noticed the lack of a single unique key field on |
| "#352-4635A"? The role of the meaningless number | | | | child records. For example, the child of order header |
| is to get auto-incremented in 1 line of code, being | | | | would have a key combination of HeaderID and |
| totally hidden from the user, and the foundation of | | | | Linenumber. If I applied the same consideration to the |
| the relationship to it's parent database table. The role | | | | CallHist table, then there would be a combo of |
| of the Speeding Ticket number is to humanly find a | | | | RolodexID and Call Date as the first index. What [the |
| record more easily than 12342345345565. | | | | used] this technique does is to form the basis of a |
| Table Layout Proposed: | | | | future child table to CallHist. From the get-go, a |
| -Fines- | | | | strong ID field exists to form a potential one-to-one |
| Column: Element: Type: Length: Index: Relates To: | | | | column relationship to a child of CallHist which may |
| 1 ID Numeric 16.0 1 (unique) | | | | exist in the future. The point, then, is to always |
| 2 TicketNo Ascii 20 2 (unique) | | | | program an ID column as a unique meaningless |
| 3 VIN_ID Numeric 10.0 3 VIN.ID // Not VIN (Ascii | | | | number for every table and index it accordingly. |
| Field) | | | | Then, progress onwards to specifying all the other |
| 4 SSN_ID Numeric 10.0 4 SSN.ID // Not SSN (Ascii | | | | columns of the database table. |
| Field) | | | | As an extra bonus, you will see that "recnum" or |
| -VIN- | | | | record number is not defined in any of the indexes |
| Column: Element: Type: Length: Index: Relates To: | | | | making a move from any one back end to another |
| 1 ID Numeric 10.0 1 (unique) | | | | manufacturer's back end a relatively easy job. |
| 2 VIN Ascii 20 2 (unique) | | | | Whitepaper Summary: |
| -SSN- | | | | I believe I have defined the difference between a |
| Column: Element: Type: Length: Index: Relates To: | | | | meaningful number and a meaningless number and |
| 1 ID Numeric 10.0 1 (unique) | | | | stated that meaningless numbers have no place in |
| 2 SSN Ascii 11 2 (unique) | | | | data entry! On the other hand, the argument for |
| This structure makes use of meaningless numbers | | | | placing a meaningless number as the foundation of |
| and meaningful numbers side by side, each fulfilling | | | | your relationships overwhelms the reasons that you |
| their job. | | | | might have for designing a meaningful number as the |
| Hopefully there is a programmer slapping his/her head | | | | relationship key: It is my contention that an |
| here like the V-8 commercial. | | | | alphanumeric or string column has no place as the |
| The ROLE of a meaningless number: | | | | relationship key, and lastly that each table should |
| Let's face it. Meaningless numbers are here to stay. | | | | have an ID field for forming the relationship which |
| Everybody's got one, but programmers are still stuck | | | | totally frees you to change the secondary key |
| on what to do with them. HIDE THEM! In a relational | | | | column with data entry without compromising the |
| database model, where the child is related to it's | | | | relationships. |
| parent by the ID, the value will automatically drop | | | | Let end users see meaningful numbers only, but put |
| down to the child upon a successful save, making | | | | your foundation on "meaningless design" with [hidden] |
| display of this number absolutely unnecessary. I | | | | data architecture! |