Re: Demo: Modelling Cost of Travel Paths Between Towns

"Neo" <neo55592_at_hotmail.com> wrote in message news:4b45d3ad.0411121454.4bce3508_at_posting.google.com...
> > > In RM's current solution, symbol and town name are the same thing
> > > resulting in inflexiblity in above case. In XDb2's solutions a symbol
> > > or string names a town which is a different thing than the symbol or
> > > string. RM has its own advantages which I did not list here.
> >
> > What you are not realizing is that "town" would never be a Primary Key,
so
> > it is easy in the RM to have more than one town with the same name. No
big
> > deal.
>
> True in general, but not when using the one-table schema provided by
> Mr. Celko. Please show how the current RM solution can work if two
> towns are named the same (ie change name of town c to a).

First of all, towns almost never change names. The two that come to mind in the last 50 years in the U.S. are Tucumcari, NM to Truth or Consequences, NM, and Levittown, NJ to Willingboro, NJ. Anyway, there are several ways, depending on what you are modeling, but let's assume we are modeling U.S. towns. The table would be thus:

town PK
state PK
main_postal_code PK
(Every town and city has a "main" post office. In Philadelphia, PA, which has, I don't know, maybe 52 zip codes, the main post office is 19104 (there is a 19101, 02, and 03, but they are not the "main" post office)). So, the data for Philadlphia, PA would be:

Philadelphia PA 19104

Huh? New information just in? There's suddenly another Philadelphia in PA (Happens a lot with Springfield. Many states have more than one.)

Add a row

Philadelphia PA 18952

What's that you say? The 19104 town name has changed to Neodelphia so as to not confuse you? Well, first, you may want to capture the date this occurred, so I would add a column (or two, depending)

start_date
end_date

So, now, we'd have data something like:

Philadelphia PA 19104 6/1/1682 11/12/2004 Neodelphia PA 19104 11/12/2004 12/31/2999

There is no problem, as much as you would like to have one to solve. And, no- storing PA twice is not redundant. It is part of the PK, and being so, helps to uniquely identify the town, so is therefore critical information. Received on Sat Nov 13 2004 - 03:37:46 CET