Best way to associate multiple records with one another

Question

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?

Answer 1

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'

Answer 2

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.