DB Design help - again

[simflex1]

The great Scott Pletcher helped me with this here:

Issues with DB Design - Still struggling with it

No. You'll still want a unique ID for each Toilet. And the individual toilet info never goes into Users, only into Toilets. Only summary data goes into Addresses, and, if you want to do it, summary data into Users, although that could be trickier to maintain if the same User had multiple Addresses.

Despite his incredible help, I am still having issues stringing everything together.

I thought it would be more helpful to just start from the original document the user presented to the original designer.

Here is screenshot.

I know that I can create a one to many relationship between the applicant (Onwer) and the Toilets (Toilet 1 through 5). The screenshot showed 3 but two have been added since the original design.

The question is how do I relate the applicant info with toilets and the toilet model info?

As always, any assistance is greatly appreciated.

Here is the form that applicants are given to complete and the DB design is developed from this form.

This is what I am able to put together based on the screenshot above:

Table: Applicants
ApplicantID int IDENTITY(1,1) NOT NULL,
CustomerAcctNo  DECIMAL(9, 2) NOT NULL,
ApplicantFirstName  VARCHAR(50) NOT NULL,
ApplicantLastName  VARCHAR(50) NOT NULL,
ApplicantMI  VARCHAR(5),
InstallAddress   VARCHAR(150),
City      VARCHAR(50),
State    VARCHAR(50),
ZipCode   VARCHAR(5),
DayPhone VARCHAR(12), 
EveningPhone VARCHAR(12),
IsPropertyOwner  bit,
OwnerFirstName VARCHAR(50),
OwnerLastName VARCHAR(50),
OwnerMI VARCHAR(50),
OwnerEmail VARCHAR(5),
MailingAddress VARCHAR(150),
City VARCHAR(50),
State VARCHAR(50),
ZipCode VARCHAR(5),
OwnerDayPhone VARCHAR(12),
OwnerEveningPhone VARCHAR(12),
YearHomeBuilt int,
NoOfToilets int,
NoOfToiletsToReplace int


Table: Toilets
ToiletID int IDENTITY(1,1) NOT NULL,
ApplicantID int NOT NULL, --FK to Applicants table
GPFID int NOT NULL, --FK to ToiletGPF table
ToiletModelNo  VARCHAR(50) NOT NULL,
TankModelNo  VARCHAR(50),
BowlModelNo  VARCHAR(50)

Table: ToiletGPF - Lookup table
GPFID int IDENTITY(1,1) NOT NULL,
GPF   DECIMAL(9, 2) NOT NULL,
Rebate   DECIMAL(3, 2) NOT NULL

Any recommendations for additional changes greatly appreciated.

[simflex1]

Any assistance anyone?

[robert_volk]

What advice in particular are you looking for?

What you have now should suffice for the needs you've described. I would suggest that the Rebate should be attached to the replacement Toilet, and not specifically to the GPF table, as a customer may be entitled to additional rebates beyond the default.

Alternately, you could add a RebateAdjustment column to the Toilet table and then add the 2 columns together to get the total rebate.

Edit: I suppose you could just create a new GPF row with the adjusted rebate and then assign that to the Toilet. That may or may not be confusing however, as the GPF value would appear multiple times.

If the toilets are covered by any warranty, or otherwise have a manufacturer serial number, you'd want to include that in the Toilet table.

What happens if a replaced toilet is defective and needs to be replaced again? Is that a new Application? Or is it an additional row to the Toilet table for the same ApplicantID?

The invoice form has additional data on purchase/installed dates, purchase source and plumber/installer, but they're not reflected in the DDL you've posted so far.

There's room for additional normalization, but that would require quite a few more tables and would need further information from you to determine if it's necessary.

[simflex1]

@robert_volk

I would suggest that the Rebate should be attached to the replacement Toilet, and not specifically to the GPF table, as a customer may be entitled to additional rebates beyond the default.

Thank you for your response Robert.

I would start with above comment from you.

The reason Rebate is on the lookup table (ToiletGPF) is that the users of the app already know what the toilet sizes (GPF) and the rebates associated with them.

So, according to theme, to avoid the possibility of a user entering the wrong rebate amount, the GPF and Rebate should be on the ToiletGPF table.

The way we intend to set things up on the front end is that if an applicant upgrades his/her toiltet to say 1.30 GPF (Gallon Per Flush) and the Rebate is $50.00, then selecting 1.30 GPF from the dropdown as we intend to make it a dropdown, then the amount will automatically populate the Rebate textbox.

What happens if a replaced toilet is defective and needs to be replaced again? Is that a new Application? Or is it an additional row to the Toilet table for the same ApplicantID?

Great question.

From my little experience in accounting, I would think there would be a new application for modification request and that should be a new row? but attached to the same CustomerAcctNo. This is where I need you guys great design ideas on the best approach to situations such as the great question you raised.

The invoice form has additional data on purchase/installed dates, purchase source and plumber/installer, but they're not reflected in the DDL you've posted so far

You are correct. I will add those to the Toilets table or should it be on the Applicants table.

I am happy to provide any additional questions you may have for your continued assistance.

Thank you very much

[robert_volk]

1. Regarding the replacement of a toilet, if it's a new application, what would indicate it's a replacement of a previously rebated toilet, versus adding a new, rebateable toilet in the same location?

2. Would a replacement toilet still receive its own rebate? (this would be double-dipping on rebates...hence why I suggested having a rebate adjustment on the actual toilet, you can make it negative too)

3. For a large enough property, with many toilets to be replaced, might there be a cap on the total rebate? Even if done as multiple applications, but under the same owner.

4. Would you need different Applications to replace multiple toilets if they can't be done on the same day or by the same installers?

5. If a toilet is replaced with a different GPF, or otherwise different rebate amount, how to account for the difference in rebate?

6. Suppose a toilet has detachable tanks with different GPFs, would that affect the rebate if replaced/repaired? In theory, the tank determines GPF, the toilet is still technically the same.

7. What about tanks/toilets with multiple flush volumes? (1 GPF and 1.6 GPF)

8. Can an owner or occupant install their own toilets? How to indicate that? Would it affect the rebate, especially if they botch it and needs replacement?

Depending on the answers to these questions, you'd probably want to create additional tables for things like WorkOrders or Installations, and indicate replacement/repair/etc. somehow and relate it to the Applicant and/or a previous Installation.

And while the following may not matter, it may be worth asking:

The invoice form mentions HUD, which is a regulated housing program. Any such program, like subsidized/section 8, may have additional rebates or rules that affect rebates. Beyond that, they may also require specific toilet models, approved installers, etc.

There may also be considerations for managed properties, where a company does not own the actual property but they administer it, like an apartment complex, strip mall, or gated community. If installations and replacements need approvals, it may not be accurate to consider them the owner.

Also, are these rebates being offered by a single utility company or regulator? Are they located in a single city, county, state? Are there different regulations depending on geographic location?

Hopefully these questions don't complicate things too much. These are the kinds of things I've had to deal with in the past while working at a utility billing vendor. Water in particular had all kinds of craziness, as it could be managed by city or county.

Edit: again, not to complicate things, but I forgot to mention: taxes. Energy saving home improvements often have tax incentives, deductions, and so on. If you need to record these installations for tax purposes, you'll have to handle tax IDs, and that's an additional security headache. You absolutely DO NOT and CANNOT store them in plain text in your database.

[simflex1]

Hi Robert,

I will attempt to answer your questions by referencing them by question number.

1. If a toilet at a particular property has been replaced with a rebate, then the property address is already on the system. Once you enter that address, that information will be displayed for the user reviewing the application to see.

2. No, once a rebate has been issued for a toilet, it cannot be given another rebate and as I understand it, you can replace the total number of toilets in the property has already been determined. By the way, the maximum rebate a home owner can get is 3 regardless of whether the home owner has 3 or 10 toilets. Of course you can receive one rebate for toilet or two for two toilets.

3. Great question here. I did answer this question on #2. Three is the cap regardless of how many toilets you have at that home.

4. Yes. Before a user applies for rebates, s/he would have already installed the toilet upgrades. That's why the screenshot I posted requires that the model # installed be submitted. So, if you have three toilet rebates but you send in application for two that have already been installed, then only those two will be processed. Once the third and last one has been installed, you send in another application for a rebate for the third one. So, the answer is Yes, you would need a different application.

5. Another great question. Let's say for instance, there are three toilet replacement rebates, and let's say that the GPFs are 1.28. 1.60, .80, only 1.28 has a rebate amount of $100 or less.
   The remaining GPFs are $50 or less. I am not sure why they do 100 or less and 50 or less but to keep it simple, 1.28 GPF toilet is $100 and the rest is $50 each.

6. I cannot answer that. I will have to run this question by the folks asking to build this app. What I can say so far is what I answered on #5

7. Please refer to question #5. Hopefully, I answered this question there

8. Another very important question. I will run this question by the owners of this app and I will post their response.

Depending on the answers to these questions, you'd probably want to create additional tables for things like WorkOrders or Installations, and indicate replacement/repair/etc. somehow and relate it to the Applicant and/or a previous Installation.

As I understand it, for a home owner to get a rebate, s/he just need to replace his or her toilet for ones with the improved GPFs I mentioned earlier. Repairs do not count.

The rebates are offered by county government.

All that the home owner is required to provide (not to us) is home purchase receipts to show s/he actually purchased the property that s/he is applying for rebates for.

Thank you for these very important questions.

Based on these questions, how should the DDL I posted look like now?

[robert_volk]

Thanks for explaining, I think you've covered all the questions I had. Based on that, I think the Plumbers/Installers info should be added to the Toilets table, in case you need to distinguish multiple different installation dates/people. I don't see any pressing need for additional tables.

Perhaps also add a Comment column to the GPF table, in case you have different rebates for the same GPF, the comment can clarify which one is applicable. Probably also should have a unique constraint on the GPF and Rebate columns combined.

If you wanted to enforce the cap on 3 rebates per property, you could add a ToiletNumber column to the Toilets table (tinyint is fine), add a CHECK constraint to keep it between 1 and 3, then add a UNIQUE constraint on ApplicantID,ToiletNumber. Your app would have to create/increment this number, or write/modify a stored procedure to do it. (Technically it only enforces this at the Applicant level, not the Property level, but you'd have to make several DDL changes and it's probably not worth the effort)

Since the county is managing the rebates that should answer the tax question, they would have to handle that on their side.

[harishgg1]

there are certain thought processes that are involved ..
same thought process over and over again

if you develop thinking skills .. this becomes very very easy
PHD subject
in everything from cooking playing sports any damn thing

any how

Each thing you see on the form ? you have to think in certain ways
Table
column
datatype
data
primary key table
foreign key table
performance
datawarehouse concept table
primary key
indexes
composite index
lot of transactions .. one way of doing
reporting purpose .. another way of doing

and many more are there but maynot be relavant

[simflex1]

Interesting comments

[harishgg1]

[simflex1]

Thank you very much for your assistance Robert.

Just to clarify, ToiletGPF is going to be our lookup table.

Here is how the sample data will look like:

GPFID                                 GPF                                  Rebate 
1                                     1.28                                  100.00
2                                     1.60                                   50.00
3                                     0.80                                   50.00

The user will be provided the ability to add more GPF and Rebate to this table.

On the front end, a user will select GPF from this table based on the GPF upgrade the applicant made.

So, I do not think anything additionally needs to be added to this table.

You have been incredibly helpful. Thank you

[harishgg1]

how is it physically going to happen ?

stored proc .... parameters ...
user selects from drop down
it invokes stored proc
makes the changes to the database

something like this

un fortunately .. everything has to be specified ..specified ..specified

[simflex1]

Stored procedure may or may not be required here and the dropdown is not an isolation in this case.

The is going to be a situation where the applicant enters all the relevant personal information, chooses the GPF, and based on the GPF selected, the rebate for that GPF automatically populates a textbox.

Applicant enters rest of toilet information like model #, date of purchase, purchased from, etc and then stores on the database.

If the user has more than one toilet to apply for rebate, after entering information about the first GPF, the user clicks to add another row to enter next GPF and related info.

I think you get the gist. My concern is to ensure that I have designed the DB correctly so that we won't have to deal with the situation we are currently dealing with right now where the original developer of this app obviously knows NOTHING about database design and this led to spaghetti coding