# Approach to Analyzing Database Requirements

database-designnormalizationrelational-theory

When presented with requirements for a database driven system, I always find it useful to imagine a world with no computers, where data records were kept in filing cabinets and data was collected by filling in certain forms. Then from that, I proceed to build my table of unnormalized data, then finally get it up to 3NF (if I can).

But the difficulty I often run into in my approach above is, how big should I make my forms? For example, let me use the same scenario from my last question, but extend it further.

An animal charity that aims to house rescued pets to new loving home owners. This charity works slightly differently, it has three types of variations on "Person".

1. Temporary Homeowner [TH]– Will take care of the pet temporarily until a new permanent homeowner is found.
2. Permanent Homeowner [PH] – Can view the files of all available pets at the shelter and pick one to adopt permanently.
3. Charity Sponsor [CS] – Sponsors money to the charity because of the good work they do!

Slightly different information is recorded for each type of person. The [TH] comes and registers himself/herself at the charity to help out, he/she states what type of animal he/she are willing to temporarily look after and how many animals max they're allowed to temporarily house.

The [PH] needs his/her details recorded, so that the charity can give them a call back whenever they're free to see how the poor animal is settling in.

The [CS] also needs his/her details stored in case the charity sends them a newsletter and / or a thank you note for the help and support and informing them how their money is being used to do good.

So in a world before computers, I think in such a case, there would be a "person" registration form, but to highlight my issue, how big should this form be and what information should it capture? Here is what I currently envisage this form to be like (created in word):

I suspect that my approach to getting the unnormalized data into a table would affect how smooth the process of normalization progresses later on.

From the above form, I can bang the whole thing into a table, where each form field would equate to a column of the table, but that would lead to many null values for rows of data, depending on what the person type is [TH][PH] or [CS].

I am looking for some guidance on this, and would greatly appreciate some advice on the best approach to take.

You could have a base table to store the common "person" attributes, and then specialized tables for the more specific fields. Example:

Person
------
id
ref_num
reg_dt
postal_code
phone_num

th_person
--------
id
person_id (FK to person.id)
max_accepted_at_one_time

animals
-------
id
accepted_by_th_person (FK to th_person.id)

ph_person
---------
id
person_id (FK to person.id)
animal_ref_num

cs_person
---------
id
person_id (FK to person.id)

donations
---------
id
date
amount
donor_id (FK to cs_person.id)


This might be a good starting point.