I am currently working on a database that deals with child day care and I am not sure about the design of the data.
There are several child care places and they can be classified as one of these:
day nursery
child care professional (working alone and at home)
child care professional homes (I am not sure if this term is the perfect translation, but essentially those are two carers working together from one of their homes)
My idea was to put them all into one table called "child care places" and those places can have one or more carers.
What makes me uncertain is that the children that go to the "day nurseries" should be connected to the place while those who go to the carers working alone or together at home should be connected to the carer.
Would you still use this design? (see screenshot: I have put the table "placement" in between as a child can have many carers and a carer many children)
Or would you put the day nurseries into one table and the people working from home into another?
The Join Table allows the most flexibility - A Care Place might have multiple Carers, and a Carer might work at multiple Care Places.
Join tables are usually very simple, just sets of foreign keys (FKs):
JoinTable::CarePlaceFK
JoinTable::CarerFK
Edit to add:
To use this design, a "child care professional (working alone and at home)" would be entered as its own Care Place, but one with only one Carer. For "two carers working together from one of their homes" you would create one Care Place with two Carers, etc.
An even simpler design would reflect that fact that a person might be both a Child and a Carer.
Care Places -- Join Table -- People
The People table would need to have additional fields reflecting the role (Child? Carer? both?)
Thanks for your reply. I agree on the join table between places and carers. I had planned this already, but had not mentioned it.
The other one is more difficult: In your model the child would be connected to the place. I had had this idea as well, but in reality the child is only connected to the place in the case of day nurseries.
If two (or one) carers are working at home, the child has to be related to one of them (thus to the carers table), because it is linked to the schedule this person provides.
The carer's schedule does not play a role in the case of day nurseries.
ERD is all great, but modeling the real world is tricky...
Imagine a child who is also a caregiver (they volunteer on the weekends for one of the care locations) but is also under care.
The child is cared for by a two-person caregiving team. However one person of the caregiving team travels and has no location, instead they have a daily route where they visit multiple child care locations, and the child comes with them.
this is a tricky one for sure because the relationship graph could get really messy. I believe this one will take a lot of scripting and UI and mechanics. You might be able to get away with a single join table that takes care of both inventory and placement.
Build Inventory
new profile
is the profile address a potential location? (create location)
OR
new location
Search existing profiles and join, or create a new profile.
OR
edit existing location or profile
Placement Wizard
add all profiles for placement. If the profile has multiple roles, prompt for role selection
select available location from selected profiles or search for a different location.
Assuming the placement grouping repeats, you then have the grouping related to an event table. — then there might be this issue of conflicts.
sorry for taking this long. There was a lot going on, holiday, illness,...
No, this is not needed.
I think it would be easier if that would be the question. But so far it seemed that it is not.
If a child is placed with a single child care professional (working at home): The child is linked to this carer.
If a child is placed in a day nursery (where several carers work): The child is not linked to any of the carers, because they all work there and care for the children. There is no clear single reference person for the child.
If there was a single reference person for every child at the day nurseries it would be far simpler, as it would basically be the same structure as in the other case (child care professional working at home). The only difference would be that there are several care workers at this place.
I hope I am clear enough in my explanations...
I will re-talk about this topic with my client. There might be the chance that they could specify a single reference person at the day nursey for each child.
My point about the authority ( or license ) to care for a child is this. The authority may be to an individual, such as a single child care professional, or to a business such as a day nursery.
The model I provided links the location with the "authorisation to care." The authorisation to care is the legal and professional license. You may or may not need to carry the actual licensing details. In the model that part is not important. The important feature is that it allows you to model for both individuals and for businesses.
An individual probably has a personal license to operate from a single location. A business may have a license to operate from several locations. If you have a situation where a location has several licensed individuals operate, then you will need a join table between Location and Authority.
thank you very much for the extent of thought you are putting into this.
I have clarified the situation with my client: A child is indeed linked to a day-care center (and not to the carer). It may be later but in the beginning it is not clear who the reference carer will be and therefore it is linked to the Day Care Center as a whole.
I have tried for a while to understand the model you provided, but I am not sure if I completely do.
To me it seems as if there is one missing component in this model: the "employee relation". Is it correct that I can not see which carers work for a certain business if there are no children linked to them?
Maybe I should do the following:
Link the children to the Day-Care Center table. As there is no such record for the independent day-care professionals, I will simply add a fake "business record" for them (i.e. a fake Care Center). Like this I could use the same structure for all the cases I mentioned in my original post:
Maybe there could still be another relation between the carer and the join (i. e. the children) to specify the main reference person for each child or to specify which carer has the right to add notes or see sensible information regarding the child.
But this would then be optional.
Are there any drawbacks to this approach that I do not see?
The link isn't shown in my diagram, but you can easily add a relationship between carers and the business. You don't need to have children linked between them.
You could do that. If you can achieve everything you need to do then that is OK.
Any design is making a trade-off between structure complexity and data complexity. If your table relationships don't tell the story, then the data needs to. If you struggle to capture the data you want, or to get the reports you need then you probably haven't got the right structure.
I can really relate to that. I have experienced this often enough with projects that got bigger and bigger over time with sudden needs that had not been there before (and that lead to structure changes).
But making those changes within a complex database is no piece of cake. Therefore I am anticipating and thinking about it as hard as possible. Maybe sometimes too hard...
