Wiki Home

Repeating Fields

Namespace: Wiki
In a table, repeating fields are named like Contact1, Contact2, Contact3 or Phone1, Phone2.
If you've never used Repeating Fields, raise your hand, and you get a silver star and a cigar.
There is another way this type of design flaw manifests itself that is not so obvious. We had a case where we have a table that holds the data for tour booking information. The procedure was that one person would book the tour, another would verify it and later a third would confirm it. So there were 6 fields (3 char(3) and 3 datetime) to record when these things were done and who did them. Then the procedure changed and management decided they wanted to add another step. The person who booked the tour was no longer the person who scheduled it, so we needed two new fields to record the initials and datetime when the tour was scheduled. Not all types of tours need this extra step, so adding the two fields wasn't the correct solution either. We created a new table named achievements (I am open to a better name [1]) which has 4 (how about 5?) fields, a surrogate primary key (of course), a foreign key (link to the tour table), achievementtype, initials and datetime. Now management can define any number of steps they want for various types of tours.
Now, how many of you have used Contact1, Contact2, and Contact3 and had the client call you six months later with a customer that had 4 contacts? Following the First Normal Form provides for data structure stability over time and allows a business to grow without requiring changes to the data structures.
In addition, don't limit normalization to repeating fields within a single table. Very often, an application will contain tables with addresses, telephone numbers, etc. that are structurally identical. In these cases, creating a child table with a compound foreign key (what parent record in what parent table does it belong with?) works very well. An example of this type of normalization can be found in the MSDN Duwamish Books example. One of the only useful concepts in that application IMHO.
The worst case of this was a bakery delivery scheduler application. They had up to four deliveries a day, and used one huge table where each type of bread had four columns, plus each type of bun, pretzel... it was coming close to the number of allowed fields. One record contained the amounts for a shipping location for the day. Each day they would create a copy of all the records for the same day of last week, and only edit the changes in a single browse window, with first column (location) frozen. Each time a new sort of bread was introduced, they had to insert four new columns.
Usually, dbf files zip to about 10%. This one zipped to 1.8%. -- Dragan Nedeljkovich
See also: Database Design Problems
Contributors: Steven Black Randy Pearson jMM Jim BoothOffsite link to
John Koziol Pamela Thalacker Dragan Nedeljkovich
VFP Rookie Mistakes Category Anti Pattern Category Data
[1] I've used (and seen others use) "role" as the name for this type of entity. -- Randy P
Sounds like "Action" to me. -- Cindy Winegarden
How about 'Step'? -- Art Bergquist
I humbly suggest TheKnightsOfNi - Michael GEmmons
( Topic last updated: 2004.01.06 08:00:49 PM )