Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-05-2015, 12:54 PM   #1
dcavaiani
Newly Registered User
 
Join Date: May 2014
Location: Appleton, WI
Posts: 268
Thanks: 174
Thanked 10 Times in 10 Posts
dcavaiani is on a distinguished road
Here is Database Design Question

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 ???

dcavaiani is offline   Reply With Quote
Old 06-05-2015, 01:52 PM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,334
Thanks: 40
Thanked 3,670 Times in 3,538 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Here is Database Design Question

you would need to link on all three of State County City fields
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
dcavaiani (06-05-2015)
Old 06-05-2015, 04:01 PM   #3
dcavaiani
Newly Registered User
 
Join Date: May 2014
Location: Appleton, WI
Posts: 268
Thanks: 174
Thanked 10 Times in 10 Posts
dcavaiani is on a distinguished road
Re: Here is Database Design Question

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)

dcavaiani is offline   Reply With Quote
Old 06-05-2015, 11:46 PM   #4
ButtonMoon
Newly Registered User
 
Join Date: Jun 2012
Posts: 304
Thanks: 11
Thanked 56 Times in 55 Posts
ButtonMoon will become famous soon enough
Re: Here is Database Design Question

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 is offline   Reply With Quote
The Following User Says Thank You to ButtonMoon For This Useful Post:
dcavaiani (06-06-2015)
Old 06-06-2015, 04:32 AM   #5
dcavaiani
Newly Registered User
 
Join Date: May 2014
Location: Appleton, WI
Posts: 268
Thanks: 174
Thanked 10 Times in 10 Posts
dcavaiani is on a distinguished road
Re: Here is Database Design Question

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.

dcavaiani is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Database design question; Limit records via table design or a query or both TimTDP Theory and practice of database design 3 06-05-2013 12:35 PM
Question Database design question ekrumwied General 10 01-07-2013 01:46 PM
Database Design question merlin666 Theory and practice of database design 3 05-07-2009 02:27 AM
Database Design Question ions Theory and practice of database design 4 09-27-2006 05:20 PM
Database Design Question IP076 General 3 10-15-2004 08:23 AM




All times are GMT -8. The time now is 05:14 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World