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.
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).
Mystic last edited by
Not sure there's much to advise on for such a simple use case, but I would recommend storing the
LastNamefield for the
Childrenin 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
CountryOfOriginare 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
Parenttable 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