1

What is the best database design to associate multiple rows with another another.

Let's say we have a table called businesses.

How would I associate Business A, Business B, and Business C, to the point where I could retrieve the related models from within each business.

i.e. If a user is viewing Business C, I could retrieve Business A and Business B. If a user is viewing Business B, I could retrieve Business A and Business C. If a user is viewing Business A, I could retrieve Business B and Business C.

I've been thinking about many-to-many relationship, but I would have to create 3 records to store this relationship such as:

Business_1  | Business_2
--------------------------
Business A  | Business B
Business A  | Business C
Business B  | Business C

Is that the best way?

1
  • Not sure if I get you right, but if your talking about an n:m relationship, yes, that's the relational way to model that. – sticky bit May 7 '18 at 19:01
0

The best way I can think of is a Business to Parent Business Relationship.
Basically one table will store all of the businesses. Lets say:

CREATE TABLE BUSINESS(
BUSINESS_ID nvarchar(50) NOT NULL PRIMARY KEY
);

Then a second table which refers back to it to store any related parents. I say parents specifically because if you go down the route of making them generic relationships, you could end up with circular references. A relates to B which relates to A and so on and so forth.
Anyways, the second table would be nearly identical to the first except adding a reference back to the Business table.

CREATE TABLE RELATED_BUSINESS(
BUSINESS_ID nvarchar(50) NOT NULL,
RELATED_BUSINESS_ID nvarchar(50) NOT NULL,
CONSTRAINT PK_RELATED_BUSINESS PRIMARY KEY  
(
    BUSINESS_ID ASC,
    RELATED_BUSINESS_ID ASC
),
FOREIGN KEY (BUSINESS_ID)
            REFERENCES BUSINESS (BUSINESS_ID),
FOREIGN KEY (RELATED_BUSINESS_ID)
            REFERENCES BUSINESS (BUSINESS_ID)
)

So an example record set for Business A would be

[BUSINESS]
BUSINESS_ID
-----------------
'BUSINESS A'

[RELATED_BUSINESS]
BUSINESS_ID | RELATED_BUSINESS_ID
------------|-------------------
'BUSINESS A'| 'BUSINESS B'
'BUSINESS A'| 'BUSINESS C'
3
  • Yea that's what I planned on doing, but it just seems like an odd solution because you always have to search inverse as well to get all relationships. Plus you could have 2 records that are the same but inverted. i.e. Business A = business_id and Business B = related_business_id is the same as Business B = business_id and Business A = related_business_id. – zen May 7 '18 at 22:02
  • 1
    That's is really the situation I was referring to with the circular references. Best bet is to either identify it as a child or parent relationship but if the business requirements state that their is no hierarchy, only peer to peer relations, then you will have to work out the circular/repetitive references on the output side. – Shooter McGavin May 7 '18 at 22:06
  • Ahh ok gotcha. Just wanted to check to make sure there wasnt a better solution. Thanks for your input. – zen May 7 '18 at 22:11
1

Are the linked businesses mutually exclusive groups or an extended network?

To extend your example, say we have businesses A, B and C and also X, Y and Z. If {A, B, C} form a closed group where the user can get from any one to each of the others but not to X, Y or Z then you have a closed group. If, however, the user can navigate from any of A, B or C to, let's say, X and then onto Y or Z you have an extended network.

For the closed group scenario adding a Group_Identifier to the Businesses table would suffice. The data then becomes

Business_Name  | Group_Identifier
---------------------------------
Business A     | One
Business B     | One
Business C     | One
Business X     | Two
Business Y     | Two
Business Z     | Two

To retrieve related entities

select
  businesses_name
from businesses
where group_identifier = <the current row's identifier>
and businesses_name != <the current row's name>

If a single business can participate in several groups you have what is known as a graph. Your own and Shooter's solutions implement this scenario. However, these model a directed graph - there is a "from" and a "to" end to each combination. To find all possible links you must read the table twice - once using the Business_1 column and once using the Businesses_2 column - or double up each row - "A to B" and "B to A". (There are specialised graph database products which can handle this, but that's beyond the scope of this question.)

An alternative implementation is to have a new Business_Links table. It has two columns Business_Name and Group_Identifier. Note that this in not the same as the sample data above. That is an extension of the Businesses table. It will have one row per business. This is a new table. There may be many rows per business.

Let's say {A, B, C} form a group and so do {C, D, E}. "C" is in both groups. The data is:

Business_Name  | Group_Identifier
---------------------------------
Business A     | One
Business B     | One
Business C     | One
Business C     | Two
Business D     | Two
Business E     | Two

To find the linked businesses, read the groups of the current business, then read the other business that are also in those groups. I'm not familiar with MySQL syntax but it will be something like

select
  business_name
from business_links
where group_identifier in (
  select
    group_identifier
  from business_links
  where business_name = <current name>
)
and business_name != <current name>

Reading this table will be efficient with an index on business_name and one on group_identifier. Trying to maintain a minimal set of groupings may be tricky if the bussiness often change groupings.

2
  • I was thinking about this all day long and came to same conclusion that category/group-like organization is much better and easier to manage. It would be impossible to keep track of one-to-one relationships as discussed above. – zen May 9 '18 at 2:58
  • 1
    Thanks. If you can get one, a graph database makes all this trivial. – Michael Green May 9 '18 at 3:06

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.