Simple OO/normalization question - common fields in new table/separate object?

Question

I am working on an app and creating models now. I think this is a basic 1N normalization question but am not sure. Should I break location/address out as a separate table?

Let's say I have Parents and Children. Sometimes I will have children with no currently identified parents, and found at x location. Sometimes I will know that parents live at address y location, but then move to z. They may have known children, 1,2,3. Sometimes I will have children (1 and 2) at a shelter at location r, with known parents at location s. Sometimes I will have child 1 and not know s/he is related to child 3 at location r and s and their parents are f and need to be re-united. So parents are a one and children many. Children and parents are always at a specific location but this can change and I need to track that. I need to be able to say we found child 3 here and they moved to shelter x, then y then got reunited with parent.

Should location/address be a separate table or is it more helpful to have location/address in parent, children, and shelter tables?

• Ben Cottrell: If you are looking to represent your data in a relational database, then I'd recommend starting with relational database modelling and put your data into 3rd Normal Form - i.e. eliminate redundant data, non-key dependencies and transitive dependencies. Object-Orientation doesn't really play a part. In fact, it can potentially lead you down a bad path if you happen to fall into the trap of viewing your relational tables through the prism of Object-Orientation. Remember that your Database schema does not need to affect or constrain the structure of the rest of the app.
• Bart van Ingen Schenau: To add to the comment from @BenCottrell, a class in the software can easily map to either part of a table in the database or to a join of multiple tables.
• GHP: I agree with Ben, don't start thinking in OOP for this problem yet. Get a good database structure first, then your code can be structured based on the UI needs later. Also, OOP does not mean you need just one class for 'Parent', you can have as many classes as needed that happen to interact with the 'parent' database structures/rows.
• philipxy: Follow a published academic textbook on information modelling, the relational model & DB design & querying. (Manuals for languages & tools to record & use designs are not such textbooks.) (Nor are wiki articles or web posts.) Ask 1 specific researched non-duplicate question where stuck. Right now you are just asking for us to rewrite a textbook with a bespoke tutorial & you have shown no research effort on answering it. Please see How to Ask, hits googling 'stackexchange homework' & the voting arrow mouseover texts.

Answer 1

I'd start with separate tables representing the following:

• Person - common struct for both parents and children

• ParentChildRelationship - has col for parent ID and a col for child ID, this way you can have as many 'generations' as needed, because children can have children. In your case a 16 year old child might also have a 1 year old child of their own and you can construct a family tree if needed. This also means a child without a known parent just has a row in the Person table and no rows here (yet).

• ShelterLocation - address/details of physical location.

• PersonAtLocation - holds person ID, shelter ID, datetime for when the person arrive, nullable datetime (defaults to null) for when the person left. Update the nullable datetime to 'now' when the person leaves that shelter location, regardless of whether they go to a new shelter location (which would be a new row in this table if they do so).