Database Design for non-profit family assistance



  • I have a very basic ERD created for a simple database that stores some information about refuge families being helped out by a non-profit that helps provides some basic needs.

    enter image description here

    ID is the primary key for Parent and foreign key for Children.

    Admittedly, I'm new to databases so I'm looking for tips on how to improve this design.

    Another approach I'm considering:

    Parent(parent_id, lastName, firstName, phone, notes)
    Home(Address, Street, Apartment, City, Zipcode)
    Stats(parent_id,DateOfArrival, DateReceived, CountryOfOrgin, Status)
    Children(parent_id, child_id, firstName, gender, age, shoe, notes)
    

    But I'm unsure of the primary and foreign keys necessary to connect the tables in this scenario. It seems easier to query if I could get things connected correctly.

    An example query might be something like "select all children with the same street name" (this would make delivering supplies a little easier).

    Thank you.



  • Not sure there's much to advise on for such a simple use case, but I would recommend storing the LastName field for the Children in their respective table too for cases like divorced parents with different last names, or if a child one day gets married and their last name changes, etc.

    Also things like CountryOfOrigin are usually better stored in their own lookup table, with a unique ID per country. This allows you to have better data integrity by storing the CountryId in the Parent table instead, which points to a normalized name of the country in the lookup table. Referential integrity can be enforced with a foreign key relationship too, to ensure no invalid values are saved to the Parent table.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2