Re: Normalizing - HELP!

"gweasel" <baneaic@xxxxxxxxx> wrote in news:1171309482.245715.291190
@q2g2000cwa.googlegroups.com:

don't have any fields in a
single table that repeat. Right?

Wrong.

"Repeating fields" is a phrase often used to describe (one aspect of)
non-normal models, but it's not very precise or accurate. Specifically,
this is usually used about tables that have Owner1Name, Owner1Address,
Owner1Name, Owner2Address and so on.

Normalisation is all about making sure that all the fields in the table
relate to the (primary)* key. If you know the VehicleRegistationNumber,
then there is only one value of Make, of Model, of Colour, of
DateOfManufacture, etc. On the other hand, the NumberOfSeats or
MaxKerbWeight are functions of the Make and Model. All Ford Escorts will
have 5 seats and weigh 1022Kg, regardless of the actual vehicle it is.

This kind of cuts out some repetition: you don't need to store Seats and
Weight etc for every car, when you could keep another list of Seats and
Weight for every variety of Make and Model you are interested in. But
it's not about saving disk space: R databases will often be bigger than
their flat-file equivalents, and they will often be slower too.

The difference is in accuracy, otherwise known as integrity. It's no good
having one record of a Ford Escort having six seats and 1499Kg and being
different from all the others. Which one do you believe? For many of us,
having a database that is correct is more important than having one that
is fast. What happens when you remove the last Ford Escort from the
table? You will lose all information about the number of seats and kerb
weight for that make and model - this may or may not be an important
problem. It would be a big problem if a Doctor ceased to exist because he
went away on holiday and all his Appointments were handled by a locum.
Think of an Employees table, with details of ProjectName, ProjectStart,
etc etc: when there is a change in personnel, if the old ones are deleted
before the new people are inserted, then there won't be a Project to add
them to.

This is why non-R models are said to suffer from Insert, Update and
Delete anomalies, and why R models can be _guaranteed_ to provide the
information that they are said to provide. No, it's not always easy, and
that is why good systems analysts get paid the big bucks. It's certainly
not just a system of applying rules like "no repeating fields", I'm
afraid.

Hope that helps



Tim F

[* note for Jamie: I know that it applies to all candidate keys, but I'm
trying to keep it simple.. :-) ]
.

Relevant Pages

  • Re: Taking Sides: Should Obese Passengers Pay More?
    ... Fact is that the actual cost of flying depends on the weight of the ... factor in the operating cost of the airline. ... allow you to have lower fares for the lighter people and higher fares ... population that is fatter and requires wider seats. ...
    (rec.travel.air)
  • Re: Taking Sides: Should Obese Passengers Pay More?
    ... Fact is that the actual cost of flying depends on the weight of the ... factor in the operating cost of the airline. ... allow you to have lower fares for the lighter people and higher fares ... population that is fatter and requires wider seats. ...
    (rec.travel.air)
  • Re: Taking Sides: Should Obese Passengers Pay More?
    ... So if you fit certain dimension, you pay according to the weight, using ... then we have to give you 1.5 seats. ... they would occupy 3 seats for 2 people. ...
    (rec.travel.air)
  • Re: Sitbones
    ... We try to get as much of our weight on those sitbones as possible. ... have cut-down KH seats on my trials and MUni ... grooving and flattening the seats really helped me take weight ...
    (rec.sport.unicycling)
Loading