I'm very new to database design so there may be a very simple solution that i just cant see.

I am trying to build a simple referral tracking system

I am using filemaker

The challenge i have is how to link the referrer to the referree. As i understand it, it is a one (referrer) to many (referrees) relationship. but both the referree and the referrer are customers in the same table.

what i want is in the customer table, to be able to select the referring customer and for the referring customers record to have a list of all the other customers that they referred.

thanks in advance for your help.

I don't know if filemaker supports it, but what you want is a recursive relationship.

Customer(customerID, referrerID, CustomerName ...)

In this table the referrerID would be the customerID of the referrer of the customer. This creates a hierarchical chain of relationships within the same table.

Thanks a lot for your help

That was exactly what i needed to know. It works perfectly in Filemaker

thanks again.