Greetings again experts,
The following tables with exception of the lookup table were designed by some employee who I am told has left company. I designed the lookup table.
I have attached sample data for each table:
UserData WaterAccountNo decimal(15, 1) NOT NULL, UserID int PK NOT NULL, OwnerID varchar(8) NOT NULL, Applicant varchar(250) NOT NULL, GPFPerToilet varchar(30) NOT NULL, RebatePerToilet varchar(30) NOT NULL, TotalRebate money NOT NULL AddressData Address varchar(50) NOT NULL, City varchar(35) NOT NULL, State varchar(3) NOT NULL, Zip varchar(10) NOT NULL, YearBuilt smallint NULL, Toilets tinyint NULL, Replaced128 tinyint NOT NULL, Replaced160 tinyint NOT NULL, WaterAcctNo decimal(15, 1) NULL Pin varchar(18) NULL Lookup table ToiletID int PK NOT NULL, GPFPerToilet varchar(30) NOT NULL, RebatePerToilet varchar(30) NOT NULL Sample data for UserData table: GPFPerToilet RebatePerToilet TotalRebate 1.60,1.60,1.60 50.00,50.00,50.00 150.00 Sample data for AddressData table: Address Toilets Replaced128 Replaced160 123 Jones Dr 4 2 0 Sample data for Lookup table: ToiletID GPFPerToilet RebatePerToilet 1 1.28 60.00 2 1.60 50.00 3 1.80 50.00
Here are our concerns.
1, The userData table has three fields that we have some doubts about their designs:
I have also posted sample data for each of these fields.
I have also created Lookup table with three fieldnames.
Sample data also posted.
How do I fix the userData table with those fieldnames such that Toilet information stays with lookup table and ToiletID becomes foreign key to UserData table?
Our hope is that on UserData table, there will be one row of records for each GPFPerToilet as opposed to storing them on same row with comma delimitation.
Then AddressData table has three fieldnames that we believe can be designed better.
Those are Toilets (this field contains the total number of toilets in a particular address)
Replaced128 (Those shows how many of those toilets have been replaced and are of 1.28 GPFPerToilet)
Replaced160 (Those shows how many of those toilets have been replaced and are of 1.60 GPFPerToilet)
This got up stumped. Any assistance is greatly appreciated.
Please, please let me know if you need additional information.