Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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):

enter image description here

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.

share|improve this question
add comment

1 Answer

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
  addrs_line1
  addrs_line2
  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.

share|improve this answer
    
Thanks FrustraedWithFormsDesigner (long name ! lol). The issue is I want to arrive at 3NF. If I were doing this from a Up-Down approach such as Entity Relationship Modeling I wouldn't have much hardship. But I want this documented in Normalization, so I want to get to the ideal Database design (3NF max) via the Normalization process. Sorry, I should have made that clearer, and yes more animals could be added. –  Ciwan Mar 13 '13 at 14:55
    
@Ciwan: I added the normalizations I mentioned. I'm not sure there's much more to do given the sparse requirements you've shown. –  FrustratedWithFormsDesigner Mar 13 '13 at 15:25
    
@FDWD: Thanks you're right. The only other requirement is to store the details of the animal and track what homes it has been at and their date. Do you think it would be better to shift focus from Person to Animal? Since after all, this is an Animal Shelter? Will draw up table of normalized data shortly. –  Ciwan Mar 13 '13 at 17:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.