Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Normalization Rules (Access 2000 >)

    How many normalization rules are there? If I need to normalize a table I usually look out for and correct these:
    1. Break down fields into smallest meaningful value, and have no repeating fields in a table.
    2. Each table must be assigned a primary key.
    3. Each non-key field within the table must be directly related to the entire primary key (multiple or single).
    4. break down table into smallest table possible. (ie. A table must have ONE topic)

    Are the more normalization rules that I need to apply?
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Normalization Rules (Access 2000 >)


  3. #3
    Star Lounger
    Join Date
    Dec 2001
    Location
    Houston
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Normalization Rules (Access 2000 >)

    With rules 1-3 you have the right idea (partially), but #4 is not a normalization rule per se. I will add a couple of comments without trying to exhaust the topic.

    2) Not only must each table have a primary key, but no two tables should have the same primary key. If they do, they are part of the same logical entity and should be a single table. There are times when it is convenient to break this rule, for good reasons -- such as the creation of archive tables -- but that does technically violate normalization rules. Definition of logical entities encompasses the first rule.

    1) "smallest meaningful value" is not the best way to look at this. The rule is, no derived items -- no calculated or computed fields, whether numeric or string. Again, it is may be convenient to carry a calculated field (such as an invoice line item total), but it violates normality.

    No repeating groups is a final rule, largely because a repeating group carries an implied compound primary key that is one or more columns deeper than the table you have under consideration. Never violate this rule if there is no theoretical limit to the number of groups that can appear. In some cases it may be reasonable to carry a fixed set of fields -- such as one for each day of the week or month in the year -- but only if they are rigidly set and will not vary. Anything else will cause major programming headaches, and will limit future flexibility.

    4) This speaks to the idea of records used for multiple purposes with "record type codes". In effect, the record type code becomes part of the compound primary key. Under normalization this may sound OK, but the question is, is the record type code a contrived (that is, made up such as A, B, C, etc) value, or is it real data? A contrived value will quite possibly create legacy issues sooner or later; data with "real" information will serve better in the long run. The use of such a contrived value points to the likelihood that the logical entities have not been fully explored and defined.

    1. Break down fields into smallest meaningful value, and have no repeating fields in a table.
    2. Each table must be assigned a primary key.
    3. Each non-key field within the table must be directly related to the entire primary key (multiple or single).
    4. break down table into smallest table possible. (ie. A table must have ONE topic)

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Thanx guys....Informative

    <P ID="nt"><font size=-1>(No Text)</font>
    Regards,
    Rudi

  5. #5
    Star Lounger
    Join Date
    Dec 2001
    Location
    Houston
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Thanx guys....Informative

    I forgot to answer your question -- 3 normalization rules, hence "Third-Form Normalization". Some texts quote a fourth and fifth rule, but they are really further explorations of the first rule. Fourth and fifth form almost always overdo it, decomposing the data to a level that is difficult to manipulate.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •