Sunday, February 19, 2012

Design Question

I have been asked to build a database that tracks children and households. I have come up with the following sample and would like some input. The idea is that a child could have two parents and each parent could live a different locations (Divorced). So I need to link the mother and father records to the child or children.

My real question is how I could then query a list of client relations (household) that could then be used possibly for a directory. For example;

Jimmy Smith Student
Jane Smith Mother
Jack Smith Father

Thank you for any help.

tblClients (all parents and children are entered here)
Client_ID
etc...
tblRelations (there is a one-to-many relation on the tblClients) One client could have many related client records. The client_ID of related client records is entered into a related_ID field of the tbRelations table.
Rel_ID
Client_ID
Related_ID
Relationship (mother, father etc...)I'd set it up as the following tables...


tblClients
ClientID int identity
ClientName varchar (30)

tblRelationshipTypes
RelationshipTypeID int identity
RelationshipTypeName varchar(15)

TblRelationships
ChildID int (stores the ClientID of the child)
ParentID int (stores the ClientID of the parent)
RelationshipTypeID int

You could then create the following view


SELECT Child.ClientID as ChildID,
Child.ClientName AS ChildName,
Parents.ClientName AS ParentName,
tblRelationshipTypes.RelationshipTypeName
FROM tblClients Child,
tblClients Parents,
tblRelationships,
tblRelationshipTypes
WHERE Child.ClientID = tblRelationships.ChildID AND
Parents.ClientID = tblRelationships.ParentID AND
tblRelationshipTypes.RelationshipTypeID = tblRelationships.RelationshipTypeID

which would return


ChildID ChildName ParentName RelationshipTypeName
---- ---------- ---------- -------
1 Joey Mrs. Smith Mother
1 Joey Mr. Smith Father
2 Susie Ms. Wilson Aunt
3 Billy Ms. Jones Mother
3 Billy Mr. Steves Father

No comments:

Post a Comment