Database Design...

  • Thread starter Thread starter Jody McKinzie
  • Start date Start date
J

Jody McKinzie

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.
 
No, there is no add-in or any other automation that can correctly design a
relational database. That comes with study and experience.

Now, what you need:
tblVendor - Data specific to the vendor that is not repeatable. Addesses
are, so they should be in another table.

tblAddress - All the address information for all vendors. It needs the
following fields:

VendorID - contains the value of the primary key field of the related vendor
in tblVendor
AddressType - Billing, Shipping, Order, Physical, etc.

Since you will be placing orders with the Vendors, you will need an Order
Header table. It would carry non repeating info about the order, Vendor,
Date, etc.

And an Order Detail table. It should carry the line item info about each
item ordered, unit of measure, unit price, quantity ordered, extended price,
taxable, etc.
 
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 said:
What i want to do is seperate the data into three tables: Vendors;
VenderOrders; and VendorBilling.
Click to expand...

Reasonable enough.
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.
Click to expand...

Not so reasonable. Or rather sorta. The VendorOrder and
VendorBilling table will have a foreign key pointing to the Vendor
table. But they will each have their own, presumably autonumber,
primary key.

After all you could easily have multiple order locations for the same
customer, especially in construction and you could have multiple
billing addresses, say in different cities. You obviously can't have
vendor 1 as the primary key in all three tables then.

But what if the addresses are the same? As presumably they frequently
would be? Why not have a status code that states Billing only, Order
only or both?
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.
Click to expand...

A form and two subforms. Or if you choose to use the previously
mentioned status code then a form and one subform.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
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!"
Click to expand...

I've seen the "so help me Codd!" cliché (which can also be applied to
a 2NF design, BTW) attributed to so many people that I strongly
suspect it did not originate from our very own John Vinson. I've never
seen him claim credit for it -- have you?

Hmm, care to make a case for 3NF? Before you do, you might want to
take a look at this discussion from the comp.databases.theory archive:

Who first (publicly) asserted 3NF is "good enough"?
http://groups.google.com/group/comp...e_frm/thread/27deb59c09dc8ace/dfb5695ca86f6c1

"Would the real 3nf please stand up? BCNF is what 3nf was intended to
be except somebody goofed. If asked to describe 3nf, most folks would
describe BCNF instead."

"It's not so much that 3nf is good enough as it is that almost all 3nf
designs are also in 5nf. Violating the higher normal forms requires
compound keys and complex dependencies, and designers tend to avoid
both."

"Have you actually seen a 3NF model in production? Or have you only
seen it as the logical represenation of a physical database? I'm still
waiting to find a database in 3NF. Apparently 3NF isn't good enough."

Jamie.

--
 
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.
 
I've seen the "so help me Codd!" cliché (which can also be applied to
a 2NF design, BTW) attributed to so many people that I strongly
suspect it did not originate from our very own John Vinson. I've never
seen him claim credit for it -- have you?
Click to expand...

Nope. Like almost all my jokes and puns, it's stolen from some wiser person.

John W. Vinson [MVP]
 
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.
 
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
 
I think I know one of the residents of those towns. When I was working at
Mercedes Benz Credit Corp., we used to hear from a customer at least twice a
year regarding her city name. In fact, the company ended up changing the
address table in response. The app as originally designed stored ONLY zip
nine as the address and used the PO database to look up the actual address.
Because of this one lady, we had to modify the app to store city and state
as well so we could override the PO city/state value if necessary.
 
Back
Top