Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Schema Woes (MS Office 2000 Dev SR-1 on Win2K SP1)

    Consider these two tables:

    tParents: ID, Last, First, Address, etc.
    tChildren: ID, Last, First, SSN, etc.

    I can relate these entities with a

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Schema Woes (MS Office 2000 Dev SR-1 on Win2K SP1)

    Your bigger problem is going to be how you identify which family a member belongs to. In other words, how are you defining a family? If you don't create all the family members at once, how do you plan to assign them later? It sounds like you're defining families both as groups that share a residence and also parents/children that live elsewhere, i.e., parents who have divorced and remarried.

    Can you explain more about what the database is supposed to track? That way someone might have a brainstorm and be able to offer helpful suggestions right away.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Schema Woes (MS Office 2000 Dev SR-1 on Win2K SP1)

    Last question first: the aim is to model a school.

    I shall define a family as: 1 or more parents, with 1 or more children. Last names can differ. Addresses can differ. Sort of like ... real life!

    Having slept on this, i am thinking of this "design":
    tFamilies: FamID
    tRelations: RelID, FamID, MemberID
    tParent: parID, etc.
    tChild: chiID, etc.
    where MemberID in tRelateons can be either parent or child. Hmm... that doesn't make sense if a parent and child can have IDs that clash. egads. Maybe I can forbid divorce and remarriage... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I am assuming that I *can* assign everyone to a family at "registration" using a form - is that reasonable?

    The school in question has fewer than 100 students, so even if I have to do family assignment "by hand", it is not a big deal. Further, i can write something that presents a pick-list, so it won't be completely manual. And once done, the new registrations are automated. If I can figure out what I'm trying to do!


    The problem as i see it is the scenario in which a parent divorces & remarries another parent. So now he is a member of two families; an ex in one, and the father in the other (where there is also an ex). I am talking myself into yet another 1-many table...

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Schema Woes (MS Office 2000 Dev SR-1 on Win2K SP1)

    I believe your problem is that you have a many-to-many relationship between Parent and children. That is one Parent may have many chidren, and each child may have more than 1 parent. The only way to handle this is through a "resolver" table. (Also, you need a ChildID).

    This resolver table, let's call it tblParentchild, merely has in it 2 fields: ParentID and ChildID. It is on the "many" side of one-to-many relationships with both the Parent and Child tables.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Schema Woes (MS Office 2000 Dev SR-1 on Win2K SP1)

    Mark - you are correct.

    Let me run this by you: Suppose I "start with" 3 tables: tAdult, tChild, and tFamily. If i normalize this, I wind up with TWO resolver tables:
    tFamilyToAdult: ID, FamilyID, AdultID
    tFamilyToChild: ID, FamilyID, ChildID

    5 tables in all.

    Am I making this more difficult than necessary? Is this a sane way to deal with families? If I use this strructure, what kind problems shuld I anticipate?

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Schema Woes (MS Office 2000 Dev SR-1 on Win2K SP1)

    >>: Suppose I "start with" 3 tables: tAdult, tChild, and tFamily<<

    Peter,

    It depends on what the relationships are (no pun intended). What is a family? Can an adult be in more than 1 family? Are children associated with an adult, a family, or combination of adult and family?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Schema Woes (MS Office 2000 Dev SR-1 on Win2K SP1)

    If i could define everything clearly, I suppose there would be no problem.

    I tried a 5-table mockup, in which one of the tables was simply a list of primary keys (= FamilyID) and had problems getting any data into the other tables. Referential integrity could not be attained because one has to enter data into two or more tables simultaneously.

    The way I see it, an adult could be in 2 or more families, but the role in each family is different. I think now that i can include the same adult in different families using a 'role' field, and in those (rare) cases where the situation exists, enter the data twice. The tblParent would be:
    ParID, FamID, Role, Lastname, etc.

    But this takes me to a general theoretical question: if I look at my definition of a Parent, it seems to me that FamID doesnt 'depend' on ParID, which is why I tried to factor this out into the more abstract Families table. When does one stop factoring? Or phrased better, what is the principle that tells me to stop normailizing? Or is this one of those 'experience' things?

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Schema Woes (MS Office 2000 Dev SR-1 on Win2K SP1)

    >>If i could define everything clearly, I suppose there would be no problem.<<

    Unfortunately, this is what you have to do. First of all define the "entities". One entity might be Families. Another Parents. Another Children. Now define relationships between entities. Are children a subset of families or Parents?

    For example, Joe and Mary had 2 children. Joe and Mary divorced and each remarried. Are these 2 children part of the original John and Mary Family? Does that even exist anymore? Or are they now part of both the new John & Sally Family and the Mary & Fred Family?

    I'm thinking you need a Family table, a Parent table, and a Child Table.
    Then you need a resolver table between Family and Parent, and another between Family and Child. No direct relationship between Parent and Child! This assumes that a Child is a subset of Family.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Schema Woes (MS Office 2000 Dev SR-1 on Win2K SP1)

    Thanks Mark. I will chew on your ideas.

    The fact that families split up and re-form is obviously part of the problem in the definition of 'family'.

  10. #10
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Schema Woes (MS Office 2000 Dev SR-1 on Win2K SP1)

    I put together a little mdb to try your suggestion, and it feels about right.

    Given this arrangement, I assume that, for data entry, I need to generate the new FamilyID first, and then fill in the parents and children.

    I further pressume that i have a button (somewhere) that will open a from for entry of the parent/child data, and during the form open event, automagically enter a new record in the Family table. Is this a reasonable design/presumption?

    I decided it would be reasonable to have 4 fields in the Family table: autonumberID, DateActivated, Active, DateInactivated.

    Pointers to references gladly accepted.

  11. #11
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Schema Woes (MS Office 2000 Dev SR-1 on Win2K SP1)

    It can get a little sticky. I'd say that you would have a mainform with the family info. You would then have 2 subforms: for parents and for children. The parent subform would be a continuous form with only 1 control in detail section: a combo box for selecting the parent from the parent table. Use NotInList event to pop the Parent entry form if the parent isn't already on the list. Child subform would work the same way.

    Again, this is based on the assumption that you want unique Parent and Child records. That is, you don't want to enter a Child twice that is a member of 2 families; you just want to refer to the single Child record in the Child table.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  12. #12
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Schema Woes (MS Office 2000 Dev SR-1 on Win2K SP1)

    Just a _little_ sticky ? <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I appreciate the help & suggestions! Thanks!

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Schema Woes (MS Office 2000 Dev SR-1 on Win2K SP1)

    We have used another strategy where we use a single table to contain both families and people in a church situation. We then use a linking table to define the relationship between people and families. In this way, people can belong to more than one family, we can deal with single parent families by making the record both a family and a person, etc. The field to indicate family and person are simple binary fields. Referential integrity is achieved using the linking table - no deletes of people or families if there are any entries in in the table for either side. The linking table also defines the kind of relationship - child, father, mother, etc.
    Wendell

  14. #14
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Schema Woes (MS Office 2000 Dev SR-1 on Win2K SP1)

    Hi Wendell -

    Perhaps my programming experience limits my vision in the database area. My experience tells me that variables, code, constants, etc, etc, yadda yadda should have a single purpose. The reason for this principle is that when 'things change', the change is easier to manage. And I have found that things _always_ change.

    So the dual 'use' you are able to extract from one record is a little scary to me. But since I'm new to this DB business, i'll study your schema carefully.

    Thanks for the suggestion!

  15. #15
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Schema Woes (MS Office 2000 Dev SR-1 on Win2K SP1)

    You are absolutely correct about things changing, and from a relational perspective, one shouldn't be using a single table for multiple kinds of objects. On the other hand, the idea of a family is a pretty abstract concept, and we found that if everyone had to belong to a family, for single people or single parents, we would have two records to maintain. We actually debated various schemas for several months before we settled on the single table approach. It bends the normalization rules a bit, but it has been used for nearly 3 years tracking about 5000 persons and 1400 families. If your application won't have many single person families, then you may find it more satisfactory to have a separate family table. And if people can only belong to one family, then you don't really need a linking table - just put a pointer to the index for the family record in the people record.

    Good Luck
    Wendell

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •