Table design question.

Hi all!
I have a question about designing database tables. Suppose I have a table that I hold information regarding my corporate customers. Some corporate customers are also customers of other customers in the table. I need to save this hierarchy in my database. I thought of designing an additional table that holds hierarchical information about companies.

Suppose that my customer table is designed like this:

CustID Company Name Phone Address
------------ ------------------- ------------ ------------------

100200          Company A             545 5543      Fict. Street.
102011          Company B             11111111      

Company B is also a customer of Company A. So the hierarchy table will be like this:

Comp1ID Comp2ID
------------ --------------

100200 102011

Comp1ID field means the company with ID 100200 is the parent and company with the ID 102011 is the child (or customer of Company A).

I can add as many as hierarchical info like this. Is this approach correct? Is there any other way to achieve this?

Thanks in advance. Received on Wed Jan 29 2003 - 08:48:56 CST