 | |
#1
 
| |||
| |||
|
Relationship Question -
07-09-2004
, 02:28 PM
#2
| |||
| |||
|
|
Re: FMPro 7 Limited experience with MS Access... Lots of experience with FMPro from early versions to 5.5... Now working with FMPro 7... Wish to set up dbase for secondary school development office... Includes alumni, parents of alumni, businesses, friends, etc. that would house demographic type info... Also will have annual fund donation history, events attended (reunions, auctions, golf, etc) history, etc.... Understand establishing primary keys, etc.... Question involves establishing relationships... Not the dabse kind... But any relationships that exist between or among the individuals in the dbase... Examples - brothers or sisters, parents, grandparents, etc.... I don't believe that I can establish keys to fields in the same table... Seem to remember something from Access called a join table... How should I set up the table structure to accomplish this? Thanks, Al |
person-id, relationship, related-person-id
with relationships from both person-ids going into your 'people' table.
The Relationship field holds the actual nature of the relationship ...
brother, sister, mother, uncle.
One note: relationship records should be created and destroyed in pairs.
A sibling relationship between 2 brothers requires a record representing
person is related-persons brother, and another record representing
related-person is persons brother.
With sufficient information about gender etc, you can derive the inverse
relationship from the first relationship, but when performing finds and
working with the data it will be a pain to have to search both person
and related-person, depending on how the relationship was set up. Better
to just have 2 records in most cases.
#3
| |||
| |||
|
|
In article <WFEHc.58057$P7.36493@pd7tw3no>, 42 wrote: A sibling relationship between 2 brothers requires a record representing person is related-persons brother, and another record representing related-person is persons brother. No, that is not required. If we know that Joe is Bob's brother, then we know that Bob is Joe's brother by inference. There is no need to record this relationship twice. Assume that we have a table that records basic facts about people. It might look like this: create table people ( person_id int not null, fname varchar(128) not null, lname varchar(128) not null, mname varchar(128), sex enum('M', 'F') not null, DOB date not null, primary key (person_id) ); Now, if we're interested in sibling relationships, it's easy enough to create a table to record those facts: create table siblings ( sib1 int not null, sib2 int not null, primary key (sib1, sib2) ); Once we have recorded the fact that sib1 is a sibling of sib2, there is no point in recording the fact that sib2 is a sibling of sib1: that is implied by the already-recorded relationship between these two people. The fact that Joe and Bob are brothers is one fact, and it should only be recorded once. |
then I have to look in sib1 *and* sib2. That's twice as much work
during retrieval, and I have to do this extra work every time I want to
see Joe's siblings.
That also makes it more cumbersome to build filemaker portals and define
relationships to get the set of Joe's siblings.
Second, the user is looking at more relationships than just 'siblings'.
Is he supposed to create a table for every relationship he wishes to
track? siblings, parents, uncles, grandparents, etc...
In short, such a design, while theoretically sound, is impractical... it
makes it virtually impossible to build a single portal to view Joe's
relatives in.
If you were designing a family tree, then yes, you might want something
like that, that just tracks 'mother' and 'father' and infers all the
rest of the relationships from that.
But to simply informally track relationships in a general purpose
application, a simpler, more practical solution should be employed.
#4
| ||||||
| ||||||
|
|
In article <IFNHc.62449$P7.32998@pd7tw3no>, 42 wrote: A sibling relationship between 2 brothers requires a record representing person is related-persons brother, and another record representing related-person is persons brother. No, that is not required. If we know that Joe is Bob's brother, then we know that Bob is Joe's brother by inference. There is no need to record this relationship twice. The fact that Joe and Bob are brothers is one fact, and it should only be recorded once. First off, in your version, if I want to find out who Joe's siblings are then I have to look in sib1 *and* sib2. That's twice as much work during retrieval, and I have to do this extra work every time I want to see Joe's siblings. Computers are fast, and the extra work is trivial. The important point is that a database is essentially a collection of facts and recording each fact only once is fundamental to good design. |
work, -he- has to do to construct his database in filemaker, to look in
both fields of that table is not trivial, not simple, and not an elegant
filemaker design.
Quote:
|
That also makes it more cumbersome to build filemaker portals I can't comment on those. |
not easy to build a single portal that can view data from multiple
relationships. Given that we likely want a simple window that lists
known relatives, a data model that facilitates this is desirable.
Quote:
|
Second, the user is looking at more relationships than just 'siblings'. Is he supposed to create a table for every relationship he wishes to track? siblings, parents, uncles, grandparents, etc... Well, before we can give him useful advice about that, we'd have to know what relationships he really needs or wants to track, which we don't. |
parents, grandparents, etc"
Quote:
|
But in simple cases, sure, why not? He said the context was a secondary school, so presumably we're not concerned with generalized family trees, just fairly simple relationships between students and their parents. |
Quote:
| If you were designing a family tree, then yes, you might want something like that, that just tracks 'mother' and 'father' and infers all the rest of the relationships from that. Even in this case, that's probably the better way to go. I chose not to go into it because my point was simpler and the question wasn't specific enough to answer at much length. But I also wanted to suggest that when the original poster asked about "join tables," he was on the right track. |
create placeholders... you aren't going to do that in a database of
school kids and their families just to capture who Billy's grandpa is
when you haven't got information for the father.
Quote:
|
But to simply informally track relationships in a general purpose application, a simpler, more practical solution should be employed. My "solution" is perfectly simple: given a table of people and an interest in which of them are siblings, record the facts of the sibling relationships in another table, once each. What could be simpler than that? In the context of a secondary school, presumably the "people" of primary interest are students and their parents, and as you note, the sibling relationships among students can be derived from the relationships among students and parents, and so a "siblings" table is unnecessary. Without knowing more about what the original writer really needs or wants to do, it doesn't seem helpful to hypothesize too much. But still, the basic idea of join tables and the principle of recording facts once each are sound. |
and I agree sound. The principle of recording facts once is also sound.
The question at hand however, is whether recording inferrable facts is
sound.
In a situation where simplicity of retrieval and ease of use are
involved, then my opinion is yes, its perfectly sound. Especially since
my opinion factors in the comparative difficulty to implement the two
different models in filemaker.
Your way, you put the onus on the user to query all the explicit
relationships, and then jump through a bunch of hoops to extract all the
inferred relationships, and somehow mash them all into a single portal.
It can be done, and in some applications I'd even agree it should be
done, but from what we've been told about this problem space, I think
not here.
As you said, more information would always help, (although we've already
apparently been given more than you thought).
-cheers
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.