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