I started to read up on 1NF, 2NF, 3NF and I'm hooked. It's going to help me map out our schema I think. I'm going to get one of the books recommended by the Wikipedia article.
I have a few relationships based on composite primary keys in FMP, but they've never been repeating fields. This is strange to me. If a compound primary key were created of phone# & zipcode, the first thing that comes to mind is a calculation field.
This is a lesson I learned the hard way. I spent 2 hours this morning
getting rid of some of our repeating fields. The FM people said that
repeating fields is a carry-over from when the related database model
was weak or non-existent in FMP. Around here, people were using them
as a catch-all. Sometimes a date, sometimes a comment, sometimes an
item like address would be repetition 2 sometimes 3. Lots of messy
abuse inherited from an old database.
Well, in a contained environment, or where data is being scrubbed at entry,
it's pretty easy to maintain consistency.
You are right, however. In database theory, repeating fields are only to be
used for creating compound relation keys.
Specifically, repeating fields that are _not_ compound keys violate
the Rule 1NF, or 'First Normal Form'.
It is _good_ that FileMaker has repeating fields, because we want to be able
to make compound keys. Repeating fields are not really a hold-over from some
less-relational time, rather they are useful 'arrays' even in a
non-relational setting. (The facility to quickly get all repetitions into
an AppleScript list is very handy, for example.)
The uses you cite -- the "oh, lots of space to dump stuff" usage -- can be
very problematic and I absolutely agree with you that in multi-user, highly
relational data settings the repeating field invites unneeded complexity and
increases instances of malformed data, in addition to being a violation of
Normal Form rules.
--
Gary