March 31, 2011 at 5:44 pm
I have a partial database design issue that I can't seem to figure out and I was hoping the community would be able to help. First off, this isn't a MS SQL database (DB2 database on an IBM AS/400 with a .NET front end), but I've always got good info here and it's really just a design question. Hopefully. Here's the deal:
I have a list of vendors (up to ~1000), a list of states (up to 58), a list of counties (up to ~3100) and a list of fees (6). A vendor can potentially work in every county in every state and either have one fixed fee for all counties in that state or one a different fee for each county, or a combination of fixed fees for some counties and different fees for other individual counties (exceptions). The vendor will always have all six fees (even if some = $0.00). Here's an example:
Vendor: VENDOR “A” | VENDOR “A” | VENDOR “A" | VENDOR “B” | VENDOR “C” | VENDOR “D”
County: Orange | Sacramento | ALL COUNTIES | Orange | ALL COUNTIES | GILA
State: CA | CA | CA | CA | CA | AZ
Fee: $25.00 | $35.00 | $15.00 | $21.00 | $18.00 | $21.00
In the example above, if the user selects COUNTY= “Orange” the results should ONLY include:
VENDOR A
VENDOR B
VENDOR C
If the user does not select a COUNTY, The results should show:
VENDOR A
VENDOR C
The problem is that every SQL statement I try results in the following dropdown list or an empty dataset:
VENDOR A
VENDOR A (repeat)
VENDOR B
VENDOR C
The user needs to be able to see a list of vendors in a dropdown based on what county they select from a list *OR* vendors that serve the entire state if the user does not select a county. Make sense so far? A vendor cannot show up in the dropdown twice and all eligible vendors must show up. Once they select that vendor, they need to see the fees that vendor charges.
So, I know I can create one ginormous table for fees with vendor/county relationships (see sample data model below), but that poses two problems: 1) It can potentially have 4.5 million records, and 2) my client says "No" to a table that size that gets queried on a regular basis. I could also create a cursor or temp table and loop through some records, but that would be an ugly loop, IMHO. Another design I came up with was to create a Vendor table, a County table, a VendorCounty table and a VendorFee table. VendorCounty would have all counties the vendor served and/or "entire state" and the VendorFee table would have ONLY the counties that were exceptions. The problem, in my mind, is that the VendorCounty (VendorCountyID, VendorID, CountyID) table and the VendorFee (VendorFeeID, VendorID, CountyID, Fee1, Fee2...) table would be almost identical - the only difference would be that the Vendor fee table would *also* have fee information. That seems like really poor design to me.
Any suggestions? Any questions?
One possible data model:
Vendor
VendorID PK
VendorName
Address
City
State
Zip
Phone
State
StateID PK
StateName
StateCode
County
CountyID PK
StateID (FK from State table)
CountyName
ActiveFlag
CountyFee
CountyFeeID PK
CountyID (FK from County table)
VendorID (FK from Vendor table)
Fee0
Fee1
Fee2
Fee3
Fee4
Fee5
Comments
April 30, 2011 at 9:09 am
I do not understand the criteria behind the following:
If the user does not select a COUNTY, The results should show:
VENDOR A
VENDOR C
You might want to elaborate on this. What are the Business Rules and what data are you storing to return the desire result?
If you are returning duplicates my guess is that it has to do with your relational structure but you should include your SQL Statement and additional information so that a determination can be made.
You need a Zip Code Table and a FIPS County Code Table (buy from USPS) so that when you enter in the Zip Code the State and County are auto populated.
If you acquire these tables you Database Design will Change significantly.
I would recommend that you do a little research that you Google FIPS County Code and USPS Zip Code Files.
You will have to pay to acquire the tables but it is worth it.
I noticed that your Fee Table is in First Normal Form. I respectfully suggest that you consider breaking down that table to 3rd normal form.
You are missing some association tables such as entities related to Vendor - Fees Counties, etc.
Did someone provide you with a Requirements Analysis Document or is that your responsibility?
Please provide the Tables, Attributes, Relationships and dummy Data (HIPA).
It is very important to get your Database Design correct before you start creating SP’s UDF’s, .NET Interface and entering Data. If that does not happen it is like trying to get the Toothpaste back in the tube/
You have a very challenging situation on your hands and I feel your pain.
Regards,
Welsh
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply