Table/Relationship Design

[spence]

This is probably basic normalization design, but I can't seem to figure it out:

I have four Vendor Types: AG, IC, GB, DE

I have ten Service Types: 001 - 010

For each Vendor Type/Service Type combination there are minimum and maximum
rates, eg:
AG 001 is from $15-$23,
AG 002 is from $17 - $28
IC 001 is from $8.00 -10.00
IC 002 is from $10.00-$15.00

etc.

I'm trying to figure out how to build lookup tables that I can use for data
validation and can't figure out how to do so. Thanks in advance.

spence

 

[Chris2]

This is probably basic normalization design, but I can't seem to figure it out:

I have four Vendor Types: AG, IC, GB, DE

I have ten Service Types: 001 - 010

For each Vendor Type/Service Type combination there are minimum and maximum
rates, eg:
AG 001 is from $15-$23,
AG 002 is from $17 - $28
IC 001 is from $8.00 -10.00
IC 002 is from $10.00-$15.00

etc.

I'm trying to figure out how to build lookup tables that I can use for data
validation and can't figure out how to do so. Thanks in advance.

spence

Tables:

You can copy and paste these DDL SQL queries each into an MS Access
Query, executing each one in order to create the tables and the
relationships.

CREATE TABLE VendorTypes
(VendorTypeID AUTOINCREMENT
,VendorType TEXT(2)
,CONSTRAINT pk_VendorTypes
PRIMARY KEY (VendorTypeID)
)

CREATE TABLE ServiceTypes
(ServiceTypeID AUTOINCREMENT
,ServiceType TEXT(3)
,CONSTRAINT pk_ServiceTypes
PRIMARY KEY (ServiceTypeID)
)

CREATE TABLE VendorServiceRates
(VendorServiceRatesID AUTOINCREMENT
,VendorTypeID INTEGER
,ServiceTypeID INTEGER
,VendorServiceRate CURRENCY
,CONSTRAINT pk_VendorServiceRates
PRIMARY KEY (VendorServiceRatesID)
,CONSTRAINT fk_VendorTypes
FOREIGN KEY (VendorTypeID)
REFERENCES VendorTypes (VendorTypeID)
,CONSTRAINT fk_ServiceTypes
FOREIGN KEY (ServiceTypeID)
REFERENCES ServiceTypes (ServiceTypeID)
)

That should be able to store any combination.

If you add DateStart and DateEnd columns to VendorServiceTypes, you
can even have the rates change over time and retain a history of those
changes (in case you need to calculate something based on past rates,
or if you need to set up rate changes in advance).

Load your data.

Query Example:

SELECT VR1.VendorServiceRate
FROM (VendorServiceRates AS VR1
INNER JOIN
VendorTypes AS VT1
ON VR1.VendorTypeID = VT1.VendorTypeID)
INNER JOIN
ServiceTypes
ON VR1.ServiceTypeID = VT1.ServiceTypeID
WHERE VT1.VendorType = Forms!frmYourForm!txtVendorType
AND ST1.ServiceType = Forms!frmYourForm!txtServiceType

That should work for you.

--------------------------

You also made a comment about normalization, and so here are some
links.

Good Basics:

About.com
http://databases.about.com/od/specificproducts/a/normalization.htm

Tips to Get You Going
http://home.att.net/~california.db/tips.html#aTip1


Intermediate:

MySQL's website:
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html


Advanced:

Wikipedia:
http://en.wikipedia.org/wiki/Database_normalization


Very Advanced:

University of Texas:
I quite like this whole site, since it has a handy menu on the right
describing many important aspects of the database world:
http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html


Sincerely,

Chris O.

 

[Chris2]

SELECT VR1.VendorServiceRate
FROM (VendorServiceRates AS VR1
INNER JOIN
VendorTypes AS VT1
ON VR1.VendorTypeID = VT1.VendorTypeID)
INNER JOIN
ServiceTypes
ON VR1.ServiceTypeID = VT1.ServiceTypeID
WHERE VT1.VendorType = Forms!frmYourForm!txtVendorType
AND ST1.ServiceType = Forms!frmYourForm!txtServiceType

Oops, there are a couple of typos.

The following:

ServiceTypes
ON VR1.ServiceTypeID = VT1.ServiceTypeID

Should be:

ServiceTypes AS ST1
ON ST1.ServiceTypeID = VT1.ServiceTypeID


Sincerely,

Chris O.

 

[Chris2]

message
Oops, there are a couple of typos.

The following:


Should be:

ServiceTypes AS ST1
ON ST1.ServiceTypeID = VT1.ServiceTypeID

<sigh>

ServiceTypes AS ST1
ON VR1.ServiceTypeID = ST1.ServiceTypeID

(Normally I load some sample data and run the query to make sure my
typos are gone.)

The whole thing:

SELECT VR1.VendorServiceRate
FROM (VendorServiceRates AS VR1
INNER JOIN
VendorTypes AS VT1
ON VR1.VendorTypeID = VT1.VendorTypeID)
INNER JOIN

ServiceTypes AS ST1
ON VR1.ServiceTypeID = ST1.ServiceTypeID
WHERE VT1.VendorType = Forms!frmYourForm!txtVendorType
AND ST1.ServiceType = Forms!frmYourForm!txtServiceType


Sincerely,

Chris O.

 

[John W. Vinson]

This is probably basic normalization design, but I can't seem to figure it out:

I have four Vendor Types: AG, IC, GB, DE

I have ten Service Types: 001 - 010

For each Vendor Type/Service Type combination there are minimum and maximum
rates, eg:
AG 001 is from $15-$23,
AG 002 is from $17 - $28
IC 001 is from $8.00 -10.00
IC 002 is from $10.00-$15.00

etc.

I'm trying to figure out how to build lookup tables that I can use for data
validation and can't figure out how to do so. Thanks in advance.

spence

I'd suggest a PriceRange table:

VendorType <Text 2, part of Primary Key>
ServiceType <Text 3, part of Primary Key>
MinPrice <Currency>
MaxPrice <Currency>

with four records with the data you show above.

You can join this to your main table by BOTH fields - if you're assuming that
you need only one linking field, you don't!

John W. Vinson [MVP]