ELOEN,
Perhaps you could create a Person table that would show data for all
your people.
They all have name, address, phone number and perhaps birthday, and other
identifying
information. Each person would have an autonumbered integer PersonID.
A second table would simply show the relationship between people.
In your example of fathers and children, you could have a column for
fathers and a
second column for children. I like to have an autonumbered id in tables so it
might look like this:
RelationshipID INTEGER, FatherID INTEGER, ChildID INTEGER.
The data might look like this:
1001 2330 2338 (child# 2338 of father 2330)
1002 2330 2443 (child# 2443 of father 2330)
1003 2330 2557 (child# 2557 of father 2330)
1004 2557 3559 (child# 3559 of father 2557)
If you had multiple relationships you wanted to show, you could simply
add one more
column called relationship. Change the name of column 2 from Father to Parent,
or relative.
1005 2388 4889 Mother (child# 4889 of mother 2388)
1006 5233 6833 Aunt (child# 6833 -- becomes niece
or nephew
of aunt 5233)
With this design you would avoid having two tables for payments: Famts
and Camnts.
You could have one table called PaidAmounts:
(PersonID, Amount,Date_Sent).
You could determine payments by using a sub-select:
(Assuming a balanceDue table with personID INTEGER,
AmountToPay CURRENCY)
SELECT DISTINCT t1.FatherID AS PAYEE, +
SUM t2.AmountToPay AS AmountToPay +
FROM relationships t1, +
balancedue t2, +
person t3 +
GROUP BY t1.fatherID +
WHERE t1.FatherID in +
(SELECT t1.fatherID + -- find fathers whose children s/b paid
FROM relationships t1 +
WHERE (t1.childID = t3.personID) + -- this person is a child of this father
AND (t2.personID = t3.personID) + -- this person should be paid
AND ( ((.#Date - t3.pBirthdate)/365) < 18) ) -- this person is under 18
That would give you a list of fathers, and the amount to pay them for
their children younger than 18.
Payee AmountToPay
------------- -----------------
2330 $ 235.00
2557 $ 1,492.00
You could add a Union Select to add the amounts for the
fathers who do not have children receiving payments, and also for children over
18.
You could also use views.
You would likely make the PersonID a primary key in Person, and a foreign key
in the BalanceDue and Relationship tables. Otherwise be sure to index the key
columns to increase the speed of calculating the sub-select.
Randy Peterson
> Another example I made my own:
> Table FATHER (FATHER_ID,...)
> Table CHILD (CHILD_ID,FOTHER_ID,..)
> Let's say we have a rule that we send an amount for each child over 18,
> otherwise to their father, so:
>
> Solution 1:
> Table SENDAMNT (SENDAMNT_ID,WHO_IS (father or child),WHO_ID (ID of father or
> child respectively)
>
> -or-
>
> Solution 2:
> Table FAMNTS (FAMNTS_ID,FATHER_ID,AMOUNT,DATE_SENT)
> and Table CAMNTS (CAMNTS_ID,CHILD_ID,AMOUNT,DATE_SENT)
>
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
