Re: Database Design...

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Pat

I can 'name that tune' in two notes! Without making a federal case
(crossing state lines)...

We have a couple small communities adjacent to each other that share a zip
code (or at least they did ... and it sure messed with gettiung the
application working!).

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Pat Hartman" <please no email@xxxxxxx> wrote in message
news:eduoA4ASIHA.4272@xxxxxxxxxxxxxxxxxxxxxxx
Even zip code is iffy. I haven't had occasion to look at this situation
in recent years but in the past I ran into several anomalies that
precluded depending on zip to come up with city and state. One of them
was quite close to home. On the border of Connecticut and New York were
two small towns that shared the same post office and due to that, they
shared the same zip code even though they were in different states. The
zip code was indicative of the state where the post office was physically
located in order to not confuse the normal mail distribution system.
Someone with access to the PO zip code database can confirm whether or not
this type of anomaly still exists.

"Klatuu" <Klatuu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:291400BD-070F-418F-9659-EE787D83E7C8@xxxxxxxxxxxxxxxx
Ken,

I would disagree that State is dependent on the City. For example,
Memphis
is a city in Tennessee and Texas. Jacksonville can be found in Texas,
Mississippi, and Florida. Dallas is in Texas and Oregon. Las Vegas is
in
Nevada and Texas.

So to say that a table with a state column would suffice is incorrect.
Not
knowing your system in the UK, I can't speak to that, but in the US, the
only
realiable way would be to carry only the Zip code in the address table
which
would then relate to both city, state, and if necessary, county.

Now, however, we are into the realm on perfect database design versus
usability. I would be hard pressed to know the zip code for Salina,
Kansas.
And, many cities have multiple zip codes, so to determine the correct zip
code, you have to have city, state, and street address and a large table
of
all address ranges for each zip code in the US. This data alone would
create
a substantial database.

Thus, using such a schema would become unweildy and perform poorly. Good
database design is more than just a perfectly normalized schema. It
should
include some common sense and a review to see that the data will support
the
business model.
--
Dave Hargis, Microsoft Access MVP


"Ken Sheridan" wrote:

I believe this aid to remembering the basis of normalization to third
normal
form came originally from John Vinson:

"The key, the whole key and nothing but the key, so help me Codd!"

i.e. every non-key column should be functionally dependent solely on the
whole of the key of the table. For instance to have both a CityID and
State
column in a table of addresses is invalid because State is functionally
dependent on CityID, which is not the key of the table; State is
transitively
dependent on the key. A numeric CityID column (city names can be
duplicated,
so a natural key of the city name won't work) should reference the key
of a
Cities table, in which a State column references the key of a States
table (a
natural key can be used here as state names are unique).

Ken Sheridan
Stafford, England

"Jody McKinzie" wrote:

ok, I have 1 table with too much data:

TBL:Vendors
VendorID -> Autonumber
Name -> TXT
OrderAddress -> TXT
OrderStreet -> TXT
OrderCity -> TXT
OrderSate -> TXT
OrderZip -> TXT
OrderContact -> TXT
OrderPhone -> TXT
OrderFax -> TXT

and the same for BILLING.

What i want to do is seperate the data into three tables: Vendors;
VenderOrders; and VendorBilling. Next, I want to keep the data the
same for
all vendors...ie vendor 1 is vendor 1 in all three tables and all
three
tables require they are the same.

Furthermore, I want to create a form that has all the information on
it for
ease...but when the data gets populated, it will go to each of the
three DB's.

Also, is there an easy way to apply 3NF normalization? (IE add-in or
something?)

Thanks in Advance.











.

Relevant Pages

  • Re: Database Design...
    ... I would disagree that State is dependent on the City. ... Jacksonville can be found in Texas, ... all vendors...ie vendor 1 is vendor 1 in all three tables and all three ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Database Design...
    ... I would disagree that State is dependent on the City. ... Jacksonville can be found in Texas, ... VendorID -> Autonumber ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Database Design...
    ... nine as the address and used the PO database to look up the actual address. ... we had to modify the app to store city and state ... I would disagree that State is dependent on the City. ... Jacksonville can be found in Texas, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: OT who said this ?
    ... single parent families caused by AFDC ... Ask any big city teacher how much ... big cities in the north. ... staff on the costs of the Texas dropout ...
    (rec.outdoors.rv-travel)
  • Re: OT who said this ?
    ... by AFDC aren't educated themselves and don't want their children to ... the problem seems to be; big cities in the north. ... KIDS COUNT Director, Frances Deviney, Ph.D. briefed legislative staff on the costs of the Texas dropout crisis. ... city is getting closer and closer to where I am out in the sticks, ...
    (rec.outdoors.rv-travel)