Designing Database from a piece of paper
-
Greetings experts,
Hopefully, this is the forum for my question.
I am trying to create a fully normalized database design from the attached screenshot.
Below is my draft of what I think is normalized relational database.
Can one of you experts please assist with any additional changes I need to make to this db design?
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
GPFID int IDENTITY(1,1) NOT NULL,
GPF DECIMAL(9, 2) NOT NULL,
Rebate DECIMAL(3, 2) NOT NULL -
OwnerEmail VARCHAR(5), Do you mean varchar(50) ? Or longer ?
State VARCHAR(50) What values are allowed ? Is that better than CHAR(2) and validate against a State table ?
ZipCode VARCHAR(5) What about ZIP + 4 ? 12345-6789
County in case it gets added to the form later ?
The 4 fields at the bottom are not in the table.
-
For ZIP code, I strongly recommend NOT using VARCHAR. Use CHAR instead.
My first observation is to ask if there's a 1:1 relationship between Customer # and Applicant?
My next question would be, can they have more than 1 property this would apply to? I would think that a contractor might be doing this for several different properties and property owners.
In other words, I don't believe that you're properly identified all of the "entities" that are possible.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
OwnerEmail VARCHAR(5), Do you mean varchar(50) ? Or longer ?
My bad, sorry about that. this is a draft copy.
State VARCHAR(50) What values are allowed ? Is that better than CHAR(2) and validate against a State table ?
Actually, only one state is approved for this rebate. We can hard code the value for state.
ZipCode VARCHAR(5) What about ZIP + 4 ? 12345-6789
Great point. Will make the change.
County in case it gets added to the form later ?
Will add that, thanks
The 4 fields at the bottom are not in the table.
I added those just after posting.
What happens if there is a new owner to the house who wishes to upgrade toilets with new GPF . How do I handle that?
Thank you guys for your assistance.
-
For ZIP code, I strongly recommend NOT using VARCHAR. Use CHAR instead.
Ok, will make the change.
My first observation is to ask if there's a 1:1 relationship between Customer # and Applicant?
Great question Jeff. Customer is usually the same as the applicant. If a customer buys a house that was owned by someone else and wishes to improve toilet, s/he will apply for rebate and therefore s/he is not just customer but an applicant. I hope I answered your question.
My next question would be, can they have more than 1 property this would apply to? I would think that a contractor might be doing this for several different properties and property owners.
Awesome question! I was told you could apply for more than one property as long as the property was built before 1998 and it is a single family home. Also, the maximum toilet you can apply for rebate is 3 regardless of whether you have 3 or 10.
In other words, I don't believe that you're properly identified all of the "entities" that are possible.
I agree with you Jeff. That's why I came to you guys the experts.
Many thanks for your assistance.
-
Rather than doing it for you, I was trying to inspire you to have another look. You're provided some intelligent answers to my questions and you know the rules better than any of us do. You also know things like the question you answered with "Usually yes". Think about what do you need to happen when "Usually" doesn't happen and whether or not that's going to affect "normalization".
The other thing to consider are the rules that you've partially stated and how they affect the limits of the data.
Personally, I'd consider an entity for the property, the property owner, the applicant and, of course, the toilets. Then there's the place where the toilets were purchased and the Plumber/Installer.
You can do this.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
CustomerAcctNo DECIMAL(9, 2) ??? 1234567.02 ?
GPF DECIMAL(9, 2) ?? you expect big tanks ? 9999999.99 GPF
Rebate DECIMAL(3, 2) ?? don't you expect rabates to potentially be > 9,99
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
CustomerAcctNo DECIMAL(9, 2) ??? 1234567.02 ?
This is fine.
GPF DECIMAL(9, 2) ?? you expect big tanks ? 9999999.99 GPF
This is way too high. Should be more like (3,2) or perhaps even lower
Rebate DECIMAL(3, 2) ?? don't you expect rabates to potentially be > 9,99
That potential is extremely low.
-
Thank you very much for your insights Jeff.
I am actually not too shabby with DB design but there are certain aspects that give me some pause.
For instance, my biggest concern actually is to have you guys see if I got the relationship between Applicant table and Toilets table and the GPF table just based on the screenshot I attached.
All the other pieces of puzzle will fit in nicely.
-
Fractional account # ?
CustomerAcctNo DECIMAL(9, 2) ??? 1234567.02 ?
This is fine.
-
What exactly do you mean?
-
Just another question, in other to verify that the user lives at the address s/he says s/he lives, it is better to user CustomerAcctNo or address?
CustomerAcctNo is actually Water Account number. How do we use Water Account No to determine that the customer lives at the address s/he is presenting as his/her property?
I happen to think address is harder but would like to know what you experts think.
-
Who generates Water Account # ? Does the city assign it ?
To verify, you might have to get data from town records.
-
Thank you great helpers for your insightful feedbacks.
There is just one piece of the puzzle, perhaps, the most important that I really need your expertise on.
I am attaching another screenshot. This screenshot has two pictures, labelled 1 on the left and labelled 2 on the right.
These pictures have one thing in common.
On the picture labelled 1 on the left, the owners of this app we are redesigning are adamant that we must move at least two columns, Fund_Year and Total_Rebates and their data to new DB we are designing.
On the second image on the right labelled 2, they want us to move the address column and its records to the new DB we are currently working on with your help. Their reasoning is that when they receive an application, the only way they can verify whether this applicant has applied for rebate before is by checking that address against the address on the database.
I could create another look up table, call it Addresses and dump these addresses into that table. Then whenever they receive a rebate application, they can search this address table for address verification.
The only problem I have is when they compare the address on the application against the address on this new look up table, if the address on the application exists on the Addresses table, they would like to load the applicant's data into the system and continue processing the new request.
The table is so poorly designed that we would like to dump it entirely.
My question is whether there is any possibility that salvaging the data on these two tables and somehow get them to work with our new DB design.
I have not even remotely come close to usable solution.
-
Comparing addresses sounds like a mess, if coming from different sources.
123 N. Park st
123 No. Park street
123 North Park St.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply