Designing a multi-tier (downline) database

[Jeff @ CI]

Using Access 2000 - I need to design and implement a database to track sales
and clients. The structure of the program I am supporting with this database
is set up so that if we contract with person one and then person one sets up
a contract with person two who contracts with person three - we can track and
report on the "downline". One criteria is to set up a report that shows all
top level people with their downline.

I have created a table (ClientTable) which has basic contact information and
has the key of ClientID. I also have a field named ReferralID in which I
intend to store the ClientID of the person who signed him/her up.

In creating this application, I will need to ensure that when I create my
report for the downline, it shows the downline which is limited to three
tiers.

I am seeking advice on how to make this happen. I would also like some
hints as to how to create the report for this feature. I am comfortable in
setting up the remaining criteria for the database, but admit I am far from
Access guru.

Thanks for any help provided.

Jeff

 

[Fred]

Jeff,

I noticed that nobody answered. Hopefully I can be helpful by being direct.

For a structure question (which this) you need to describe the process that
you are trying to database. and what types of information you are trying to
store: Clients? Contracts? In your whole post there were only about
three sentences on this, the rest was sidebar stuff (including on how you
tried to do it) You also said that you need to display "downline" but didn't
tell us what "downline" is, forcing us to make overly vague guesses. Also
didn't define whether a "Client" is a person, or an organization (which can
have many people)

I think that this will probably be pretty simple once you tell us those
things. In fact your job of describing this per the above may be the
toughest part, and may even answer your own question!

Hope that helps, and would be happy to help with that info.

Fred

 

[Jeff @ CI]

Thanks Fred,

Let me do my best.

The database tracks sales (contracts). It also tracks clients (people).
Each person can have up to three levels of people under him/her. The
database will assist in computing commissions to be paid.

The downline is the mulit-tier of ClientA sells package to Client B (Tier
1). Client B then sells package to Client C (Tier 2) who then sells to
Client D (Tier 3).

The contracts are sold in one of three levels. Level 1, Level 2, Level 3.
If Client A buys Level 3, any sales he makes is commissioned at a higher
level than if he had bought at Level 2 or Level 1. Commissions vary
according to which tier a client in his down line makes a sale. In the above
example. Client A would earn commissions for the sale to B. A and B would
make money on B selling C. A, B, and C would make money on C selling D a
package.

The database will need to not only compute commissions, track clients, and
contracts, but also provide (amongst others) reports to the clients on how
their downline is doing - growing, selling, etc.

It is getting to this report that I am most worried about. My level of
expertise is that of a self taught, mediocre Access designer with 3 other
databases under my belt. Those nearly had me on a strict regimen of "Just
for Men" so that my wife would recognize me.

Hope this helps.

Looking forward to your help with a promise of great appreciation.

Jeff

-----------------------

 

[Fred]

Hello Jeff,

First, at the 30,000 ft view level, I think that your process and db would
be best visualized as the main data elements beign contracts and with clients
being a sort of giant lookup table. This is just a way a viewing a
fundamental structure, not to pretend that Clients aren't at the core
regarding their importance and your mission.

And as a recap, I think that you can have a maximum of 3 levels of contracts
which means 4 levels of people. Maybe you use a different name for the
bottom three levels ("Client") but it's probably best viewed as 4 levels of
people.

I suspect that the best solution would be one big contracts table linked to
itself multiple times. Similar to a family tree database. I think that
there is such an example on Allen Brown's posts or web site as a "family
tree" solution.

Not being fluent in that, and being Mr. Low Tech/Keep it Simple, your
limitation of contracts to three tiers provides an entre' to a lower tech
solution which my brain can be comfortable enough with to describe.

I'm assuming that initial and top level contract is Level 1, that lower
level contracts can't exists without the one(s) above them in place and that
contracts can be defined from the top down. (I.E. that you can view it such
that lack of levels below it doesn't have to redefine a level 1 as a level 3.


(I'm only describing the linking fields, add fields for whatever data that
you need to record)

First I'd make "tblPeople" table, PK = PeopleID

Next I'd make 3 contract tables

tblTopLevelContracts including TLContractNum(=PK) TLCSellerID and
TLCBuyerID fields

tblMidLevelContracts including MLContractNum(=PK), TLCContractNum (linked
to this field in tblTopLevelContracts) MLCSellerID and MLCBuyerID fields

tblBottomLevelContracts including BLContractNum(=PK), MLCOntractNum (linked
to that field in tblMidLevelContracts BLCSellerID and BLCBuyerID and fields

SellerID and BuyerID fields contain the PeopleID's of the "sellers" and
"buyers", and be linked for sort of a "lookup" function.

Now, your main report will be Level1 Contracts which will have a subreport
of Level 2 contracts which will have a subreport of Level 3 contracts. Of
course, everything will look up and print people's names. You can even sort,
group and fileter by people's names, and can make the common (higher level)
name for a group be the group header(s).

If I misunderstood something about your process, then I hope that parts of
this might still be helpful.