Here is Database Design Question (1 Viewer)

Let's say you are setting up a "Tax Entity" Table:

State County City StateRate CountryRate CityRate

WI - - .0500 .0000 .0000
WI Shawano - .0500 .0050 .0000
WI Shawano Bonduel .0500 .0050 .0025

#1 - How would you 'enforce' the .0500 to appear and be the same on every WI record - other than by CODE. If WI had NO COUNTIES NOR CITIES with tax rates, then all I would need entered is the WI - - record.

#2 - When you make a table entry in another detail table which references a Supplier Table which has all 3 of those above field, State, County and City in it - How then would you decide which of the above 3 Tax Entity records would apply to this Supplier who is from: WI, Shawano, Cecil ??

#3 - is there any hope that some kind of R.I could be set up between the Supplier and the Tax Entity Tables?? The Supplier Table has a Primary key of "autonumber". The Tax Entity Table has the 1st 3 Fields noted as the Primary key. Is there any kind of a relationship which can be enforced w/o code?

I am using 3 Dlookups and that seems to work OK. Just wonder if you think this could somehow "go south" on me ???

you would need to link on all three of State County City fields

State = DLookup("supplierstate", "suppliers", "supplierid = " & Forms!supplierinvoicehdr!VendorID)
RateState = DLookup("taxstaterate", "taxentity", "taxstate = '" & State & "' And taxstatebegin <= Forms!supplierinvoicehdr!PurchaseDate And taxstateend >= Forms!supplierinvoicehdr!PurchaseDate")
RateState = Nz(RateState, 0)
County = DLookup("suppliercounty", "suppliers", "supplierid = " & Forms!supplierinvoicehdr!VendorID)
RateCounty = DLookup("taxcountyrate", "taxentity", "taxstate = '" & State & "' And taxcounty = '" & County & "'")
RateCounty = Nz(RateCounty, 0)
City = DLookup("suppliercity", "suppliers", "supplierid = " & Forms!supplierinvoicehdr!VendorID)
RateCity = DLookup("taxcityrate", "taxentity", "taxstate = '" & State & "' And taxcounty = '" & County & "' And taxcity = '" & City & "'")
RateCity = Nz(RateCity, 0)

If State determines StateRate then you ought to have two tables instead of one. One table for the state rates; one table for the county rates. The principle that applies here is called Boyce Codd Normal Form, which requires that every determinant should be a key in a table.

ButtonMoon - I toyed with that structure, but found it wanting as well. There are 3 possible tax rates - State, County and City. To design separate country County and City tables with their respective Primary Key would likely run into problems. There could be dup counties, and dup cities for sure.