Database Design for non-profit family assistance

Question

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

Thank you.

Answer 1

One requirement and a few suggestions:

Requirement: You should have a separate primary key for the child record as well. That is, the parent and child records should both have fields called 'id'. And then, the child record should have a field called 'parent_id'. And this key should have the parent's id value. Does that make sense? You might already have this setup but I don't see the fields in the schema you show.

I would consider @JD's suggestion to have a separate table for CountryOfOrigin. Technically speaking, you could do the same for the City. But there's a caveat I'd throw in: it all depends on how you're going to be querying and what your familiarity with SQL is. Yes, the perfect normalized version of your data would pull country and city from separate tables, but that will increase the complexity of (some) of your queries because you'll have to join those tables. Similarly, many people put the entire address in a separate table because people can have multiple addresses (permanent address, mailing address, prior addresses, etc).

Personally, I'm not a normalization purist. Practically speaking, if the needs of this non-profit are simple, and their/your experience with joining tables is very basic, then don't go crazy with normalizing data. But do consider all of these possible normalizations if they make things easier for you.

For example, if conceivably your client will want to store multiple addresses, then it's worth separating that into another table. If they don't need that capability, then don't do it.

Similarly, if you think you'll want to store additional data for each country, or be querying and filtering by countries quite a bit, then it might make sense to separate that out into its own table (note: you can query very easily on a country field in the primary table, but the reason to separate into another table here is to ensure data integrity. In your current setup, there's nothing preventing one person from recording USA, another US, and another America as the country. But if you have one table with country names, and point back to it, then the database will prevent this type of data inconsistencies).

So, as always, it boils down to your specific needs :-) But hopefully these tips will point you down the right road.

Answer 2

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.