I'm just starting to design a database for a community service organization and I’m having trouble conceptualizing the relationships. The organization gives services awards to households, which are composed of individuals with differing characteristics (name, date of birth, etc). So let’s say John Doe, who is the ‘head’ of his household, and his wife Jane Doe get a food voucher to a local grocery store. So far so good.

However, let's say John gets kicked to the curb by his wife, and a few months later he and his new girlfriend show up to get another food voucher. Now, the service is provided to the new household, which again John is the head of. This needs to be a different household than the other one; if the original is just modified, it looks like John and Ms. Homewrecker received the original service as well.

This looks initially like something that I could look at as a many-to-many relationship between tbl_Clients and tbl_Households, and use an associate table to create the relationship I need. However, the two are largely dependent upon each other. The household is completely determined by which clients are in it; it might be said to have an address, for example, but that will be the same as that of all the clients in the household. It could have a total income, but that would be a function of the income associated with the clients that are member of it. And so on. The only unique thing about the household is the clients in it and in some sense relationship between them (using ‘son’, ‘spouse’, etc not the relational database term) which defines what ‘type’ of household it is.

Similarly the clients themselves have some properties (notably whether or not they are considered ‘head’ of household) that is dependent upon what household they are currently a part of. It seems like I’m trying to represent a container that is mostly defined by its contents, which in turn are partially defined by which container they’re in, and I’m completely baffled.

Can anyone offer me an insight here? I feel like there’s something very basic I’m overlooking.

Is it the case that a household always has a head? If so Household seems like a subset of the Person table not a table on it's own.

Is it the case that a household always has a head? If so Household seems like a subset of the Person table not a table on it's own.
Yes, every household will have someone designated head of household. It might be more accurate to say that every person can be a head of household, and one person within each household will be, but they may not also be the head of household of any others they have been in.

I'm working on a ERD trying to sort this out and just can't get my head around it. I keep thinking something like 'clients- --> make up households', which is accurate but not all that helpful.

I suspect the key to this is that really, at any given point in time, a single household is made up of many clients but over time, a single client may have been part of many different households (all but one of which no longer exist in any meaningful way). Unfortunately I want to be able to capture the entire history.

It would seem to me, that a "household" then is an association of persons, with a start date, and then perhaps, or eventually, an end date. I presume a person can be a member of only one household at any given time. So each household, or association, would contain a list of persons, start and end date, and then the other information relating to the household. A household has an address - inherited by all the members, and an income, which is the sum of the current members incomes. (The income of a given person surely does not depend on the membershp of a household?). A household has a "head", which is a role associated to the household, but the actual person filling it may change with time.

So you'd need to keep the persons personal (household-independent) data with the person, the household data (eg address) and start end end date with the household, as well as the data derived from the current members (total income) and the ID of the one playing the head-role. When membership of a household changes, then you have a new household - which could be related to the old one by some householdID, eg based on address.

It would seem to me, that a "household" then is an association of persons, with a start date, and then perhaps, or eventually, an end date. I presume a person can be a member of only one household at any given time. So each household, or association, would contain a list of persons, start and end date, and then the other information relating to the household. A household has an address - inherited by all the members, and an income, which is the sum of the current members incomes. (The income of a given person surely does not depend on the membershp of a household?). A household has a "head", which is a role associated to the household, but the actual person filling it may change with time.

So you'd need to keep the persons personal (household-independent) data with the person, the household data (eg address) and start end end date with the household, as well as the data derived from the current members (total income) and the ID of the one playing the head-role. When membership of a household changes, then you have a new household - which could be related to the old one by some householdID, eg based on address.
The first paragraph sounds like a good description of the situation, which is very helpful, but I'm still not sure how to think about the exact relationship. The main problem I have there is that there are certain elements that conceptually 'belong' to a person (their relationship to the head of household, mainly) that can be different between households.

More specifically, I'm thinking about Jane Doe. In the first household, her relationship to the head of household is 'spouse'. In the second, her relationship to the head of household is 'self'. So essentially, the household isn't tied to the relationships between people (which updatete over time) but to the relationships between people over a specific range of time, or at the specific point in time that the service is delivered. I think this is what you meant by households having a start and an end (once those relationships change, the household ends and a new one or more starts), right?

I suppose I could just toss the specific relationships and assign each household a 'type' value, which is what the relationships largely determine, but the information can be very valuable in some eligibility determinations.

OK, so you have some more roles in the bundle of people making up a household. Post all the types of "relations" you foresee, otherwise it is difficult to guess what is suitable. Also, can a person belong to more than one household? If not, then I still think that you have a physical household with an adress (the "abode") , and a list of members of this household (variable with time), and each person has a role maybe variable with time too, and an income (and those can be summed up), and that person, in that role, and in that household, receives whatever they receive .. So if anything changes, you could end the previous hosuehold (by giving it an end date) make up a new household at the same abode, with a new start date, and still be able to track the history of one specific person. A persons income could also be tracked with dates, so the household income could be determined by summing the incomes of the residents at the required time

Also, can a person belong to more than one household? If not, then I still think that you have a physical household with an adress (the "abode") , and a list of members of this household (variable with time), and each person has a role maybe variable with time too, and an income (and those can be summed up), and that person, in that role, and in that household, receives whatever they receive .. So if anything changes, you could end the previous hosuehold (by giving it an end date) make up a new household at the same abode,
A person can belong to more than one household, but not usually at the same time (although that is possible; what if John and Jane Doe had kids, and shared custody after they split up for example?). I also don't think it makes sense to define by 'abode' - many household will be homeless, and there can be more than one household at a given address at the same time. A household also doesn't necessarily 'end' when the composition changes, at least not conceptually. It might change or split or recombine. Eventually the database needs to be able to correctly identify 'number of households served' for funding sources, so creating multiple 'new' households whenever the composition changed would seriously hamper the utility of the database. I know that doesn't matter at this stage, but it is a concern down the road.

This really has me stumped.

I think you are confusing yourself.You need to maintain the temporal development of the data. Somewhere you have a time period where a person belongs to a househhold. I do not know what exactly is required, but the one option is either to redefine the "household", the list of "members" whenever there is a change, or that for each person, you assign to which household that person belongs, with start and end date .. or a composition of those two. Since you do not wish to tie a household to an abode, then what constitutes "the same" household? IF daddy scarpers, the long lost son moves in, granny dies, and mom gets deported to Tierra del Fuego, is this still the same "household"?

I think you are confusing yourself.You need to maintain the temporal development of the data. Somewhere you have a time period where a person belongs to a househhold. I do not know what exactly is required, but the one option is either to redefine the "household", the list of "members" whenever there is a change, or that for each person, you assign to which household that person belongs, with start and end date .. or a composition of those two. Since you do not wish to tie a household to an abode, then what constitutes "the same" household? IF daddy scarpers, the long lost son moves in, granny dies, and mom gets deported to Tierra del Fuego, is this still the same "household"?
I think the bolded is very true.

As far as the 'when is it a new household'...I really don't know. It's very poorly defined, which is part of the problem. As you're implying, something has to indicate when the original household no longer exists, and what seems to make the most sense is a change in the composition. That would force a 'new' household whenever the members making it up change, but would also mean that the 'old' household still exists in some way. So I'm going to think about the example again, John & Jane Doe --> Jane Doe and John & Homewrecker:

Household 1: John Doe, Jane Doe
Household 2: Jane Doe
Household 3: John Doe & Susie Homewrecker

That would mean that the 'household' is defined completely by the people that make it up, a kind of 'snapshot' of their associations. Even the relationships aren’t as critical in defining the household as I thought if you look at it this way, since it’s not all that important if John or Jane is the head of household, or if she comes back as his girlfriend instead of his wife later. Once the household exists, if those characteristics happen to match again they've returned to a pre-existing household. This could be contextualized temporally by service awards – at the point where they receive a service, which of the ‘household profiles’ does the group of clients receiving the service match?

I think I’m on the right track here.

Have you reached the stage of paralysis by analysis? If you have not figured all this out yet, then I doubt you will get there just by thinking. I'd suggest that you create some tables, either in db or on paper, include all the required fields, draw the relations, and then plug in a few examples to see if this works.

Update:

Also, if this is not a hobby project, but intended to be used, and some peoples income will depend on it, then consider getting professional help, because a good structure is very important. And this requires that all the substantial requirements are identified. A few rounds with a pro should get you all set.

I think you're right; I'm not going to understand the nature of the relationship better than I already do by obsessing over it longer. I just don't know how to deal with it, so I'll see what I can do and then when I run into innevitable problems come back with more specific questions.

It just offends my sense of propiety to start building the database without knowing exactly what my design looks like.

You don't build a dtaabase - you build a prototype, or more than just one, to help you understand. Some people can do it in their head, some on paper, and some by prototyping. SInce you are where you are just by thinking, now might be a good time to change tack.

And you have checked the link in this post ?: http://www.access-programmers.co.uk/forums/showthread.php?t=195190

Further to spikepl's comments and reference to the databaseanswers data models, here is a link to an approach to database design from databaseanswers -- it's part of the tutorial section.

http://www.databaseanswers.org/approach2db_design.htm

Scope, rules, sample data, test all part of "attempting to stump the data model" in my view. Some testing and adjustment is key and critical. No one designed one of these "changing/ not clearly defined" things on the first go. Expect changes and learn as the changes evolve. Anything unresolved, go back to customer and seek clarification. More often than not, they never thought of that... etc.

Good luck.

I agree fully with #14 . Many a time additional requirements emerge, once there is something to look at for the people involved.

Thanks for the advice. I had checked the link you suggested, Spike. I also found a generally applicable guide to dealing with relationships between groups of people with a sample DB at this address http://allenbrowne.com/AppHuman.html and although it's not perfect it's given me a starting point.

Alright, here's what I've decided so far. If I decide that the household is simply an association of people and any time they fall into that configuration they are implicitly the same household, and that any variation is good enough reason to call it a 'new' household (baby's born, whatever) then there's no reason to track the houshold as it's own entity.

In fact, this household looks a lot like a more like a conceptual analog of a junction table: which collections of clients received this particular service? Now, these won't be distinct. However, if I need to get a count of unduplicated households served I should be able to do so by counting *all* the services awarded during that time period and then going back and checking to see how many were delivered to the same set of clients.

That seems really obtuse and I'll admit I have no idea how to implement it, but it sounds like it'd satisfy the requirements. I've been working with it a bit but it's really my first run with many/many relationships, so just getting the basic forms working so I can work with some test data is proving to be a challenge right now.