I am trying to understand how to model a certain type of relationship.
I have a dataset that I would like to put into a database, however, I have this relationship among two columns in my data.
Here is a sample of what I am looking at:
Company_ID1 Company_ID2
9581091 53506312
9581091 961273620
79735371 53506312
79735371 79735371
79735371 135962137
46667523 9122532
55189732 9122532
71453880 9122532
77817617 9122532
77817617 79834910
79871820 9122532
79871820 79834910
98158277 9122532
98158277 458182615
134303192 9122532
187502299 458182615
As you can see company_id2=53506312
is associated with two different company_id1's. This is just one example, but there is a many-to-many relationship between the two company_Id's. By inspection, this example is the same company.
How would I model this situation so that the database finds this kind of association and is able to group them all together?
The motivation behind this is finding sales and contract by a company. So grouping by either company_id will result in the wrong answer.
How I have solved this with text files, is I have a graph where all the nodes are the unique company_id's and the edges is the tuple (company_id1, company_id2). Then I find all disconnected subgraphs in the graph and each ones of those is a company.
So I have something like this:
Company_ID1 Company_ID2 company_number
9581091 53506312 1
9581091 961273620 1
79735371 53506312 1
79735371 79735371 1
79735371 135962137 1
46667523 9122532 2
55189732 9122532 2
71453880 9122532 2
77817617 9122532 2
77817617 79834910 2
79871820 9122532 2
79871820 79834910 2
98158277 9122532 2
98158277 458182615 2
134303192 9122532 2
187502299 458182615 2
So then I groupby company and I can do summary reports.
But I would like to now move to a database and have the database do this calculation for me. So, two questions. What kind of relationship is this called, and how do I model it in my database?
One last think, these two columns company_id1 and company_id2 aren't the primary keys.