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.

    db_design

    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.

    • This reply was modified 2 months ago by  homebrew01.
    • This reply was modified 2 months ago by  homebrew01.
    • This reply was modified 2 months ago by  homebrew01.
  • 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.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    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.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    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.

    • This reply was modified 1 month, 4 weeks ago by  homebrew01.
    • This reply was modified 1 month, 4 weeks ago by  homebrew01.
  • 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.

    • This reply was modified 1 month, 4 weeks ago by  homebrew01.
  • 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.

     

     

    Attachments:
    You must be logged in to view attached files.
  • 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