Database design question

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I am making a market research database, and I want to have a table of
companies, with basic info about each company. I'd also like to track
relationships between companys, like if company A is a supplier to company
B. So I thought I would have a table called company_relations, which would
have two company fields (say, company1 and company2), and a relation_type
field to describe the type of relationship. Then, I can store company B in
the company1 field, and company A in the company2 field, and set
relation_type to "Supplier". But, that means at the same time I need to make
another record that stores company A in the company1 field, and company B in
the company2 field, with relation_type set to "Customer". I could make the
second record through a macro of VBA code, but that's more work.

Anyone have any idea of how to store this relationship with a single record?
Some other way to do it? In my industry the relationships between companies
that I am trying to track can get quite complex, so I don't really want to
get into creating separate tables for suppliers, customers, partners, etc.

Thanks in advance!
 
I am making a market research database, and I want to have a table of
companies, with basic info about each company. I'd also like to track
relationships between companys, like if company A is a supplier to company
B. So I thought I would have a table called company_relations, which would
have two company fields (say, company1 and company2), and a relation_type
field to describe the type of relationship. Then, I can store company B in
the company1 field, and company A in the company2 field, and set
relation_type to "Supplier". But, that means at the same time I need to make
another record that stores company A in the company1 field, and company B in
the company2 field, with relation_type set to "Customer". I could make the
second record through a macro of VBA code, but that's more work.

Anyone have any idea of how to store this relationship with a single record?
Some other way to do it? In my industry the relationships between companies
that I am trying to track can get quite complex, so I don't really want to
get into creating separate tables for suppliers, customers, partners, etc.

Thanks in advance!
Click to expand...

What's complicated here is that some relationships (partners) are
symmetrical, and some (suppliers) are inherently nonsymmetrical. If
Calvin is Hobbs' partner, then Hobbs is Calvin's partner too; but if
Jekyll is a customer Hyde, then Hyde is a supplier for Jekyll.

The code to add the reciprocal relationship won't be too complicated,
and unless you have millions of relationships, doubling the size of
the table won't make the database performance unacceptable. I'd go for
it that way!
 
Back
Top