0

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.

  • Have you considered using a graph database, such as Neo4J, since you seem to be familiar with that methodology? – Max Vernon May 23 '17 at 13:58
  • Are the linked companies always actually the same company? – Max Vernon May 23 '17 at 13:59
  • @MaxVernon yes they are. This is due to mergers and acquisitions. – spitfiredd May 23 '17 at 14:03
  • Is there any reason you couldn't just reduce Company1 and Company2 to one column, and use a new PK integer column? – George.Palacios May 23 '17 at 14:29
  • @George.Palacios isn't that what I am trying to figure out? How to reduced company_id1 and company_id2 to the new column company? – spitfiredd May 23 '17 at 14:57
0

You have asked: What kind of relationship is this called, and how do I model it in my database?

Your scenario seems to contain a recursive many-to-many relationship: EACH company1 may have (optional) ONE OR MORE associations with another company2. Each company2 may have one or more associations with a company1. ("company1" and "company2" are both: companies. I have only given them these names so that the relationship's "directions" become obvious) The traditional way of "resolving" a many-to-many relationship is: creating an intersection/junction.

ERD (entity relationship diagram) ERD (entity relationship diagram)

Intersection in relational model: Relational model

Doing a "fast forward" to the actual implementation, maybe the following will be useful for you:

1 - for recording all NODES in your existing model: have one table for recording unique company ids, use PK constraint.

2 - for all EDGES in your existing model: have another table for recording VALID combinations. Use 2 foreign key constraints (only existing company ids are allowed), and a PK constraint that guarantees the uniqueness of the recorded combinations. (You can also use a CHECK constraint, in order to avoid associations of a company with itself. However, your test data values , line 4: 79735371 79735371, suggest that a node can be associated with itself.)

Example implementation (Oracle 12c):

-- ~ nodes in graph, uniqueness through pk constraint ~
create table companies(
  id number primary key
);

-- insert unique values ~ nodes ~
begin
  insert into companies (id) values (9581091);
  insert into companies (id) values (79735371);
  insert into companies (id) values (53506312);
  insert into companies (id) values (961273620);
  insert into companies (id) values (135962137);
end;

--- ~ edges in graph ~
create table associations(
  co1 number references companies(id)
, co2 number references companies(id)
);

alter table associations
add constraint apk
primary key (co1, co2);

testing

-- use your test data 
begin
  insert into associations values (9581091, 53506312);
  insert into associations values (9581091, 961273620);
  insert into associations values (79735371, 53506312);
  insert into associations values (79735371, 79735371);
  insert into associations values (79735371, 135962137);
end;

-- Optional: testing the intersection/junction by inserting all possible combinations:

insert into associations
select 
  c1.id
, c2.id
from companies c1, companies c2;
| improve this answer | |
  • I added some more test data, I was wondering how I would also add in the third column company_number – spitfiredd May 23 '17 at 22:39
  • (By adding the data and changing the column name you've lost me :-)) Please clarify: [1] Can each of: id1 or id2 or company_number IDENTIFY a company? [2] Are you trying to track something like (en.wikipedia.org/wiki/Multigraph)? [3] Would it be correct to say: company_number 1 deals with contracts/sales that the companies 9581091, 53506312, 79735371, 961273620, 135962137 are involved in? – stefan May 24 '17 at 0:01
  • [1] Yes [2] I don't think so but I'll think about it. [3] yes Lastly, company_id1 and company_id2 are natural keys and company_number would be a surrogate key. – spitfiredd May 24 '17 at 1:20
  • [4] Focus on "entities" (rather than "keys"), then you may find the solution quicker. If you have something like a mother-father-child situation, all 3 may be in the same category: PERSON - or, for modelling purposes: entity PERSON. For finding "associations", an intersection FAMILY (or DESCENDANTS) may do the job. NAMING of entities and (their) attributes is very important. Find correct and descriptive names. – stefan May 24 '17 at 6:31
  • [5] If you think that adding a third column to the "associations" table would give you the solution, then try it out. Implement and test it. [6] Right now, all "key" values look just like integers. Ask yourself: can there be any overlap/collisions of id1 / id2 /company_number? (If so, you will probably end up with 2 or more columns for storing them.) – stefan May 24 '17 at 6:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.