How to Make SQL Many-To-Many Same-Type Relationship Table

Deal with many to many relationships in the same table

You might want to create 3 tables. One that stores the business names, another for the adresses, and then a junction table called, for example, business_adresses.

create table businesses (
    id int primary key,
    name varchar(50)
);

create table adresses (
    id int primary key,
    street varchar(200),
    city  varchar(200),
    country varchar(200)
);

create table business_adresses(
    business_id int,
    adress_id int,
    primary key (business_id, adress_id),
    foreign key (business_id) references businesses(id),
    foreign key (adress_id) references adresses(id)
);

With this set up, each entity has its own table, and information is not duplicated, unlike when using a single table. Meanwhile, in the junction table, you can efficiently enfore the unicity of business/entity tuples through the primary key (could also be a unique key), and maintain data integrity with foreign keys.

SQL - How to handle multiple many-to-many relationships

I would go with option 1 and do join queries.
For the example query:
from a person, select precautions tied to them

you should get the person's activities from join table Person and Activities on person id and then join that with risks on activities and finally join that with precautions on risks. In the end, you will get the precautions associated with a person.

Many-to-many on same table

meaning that unique keys become flawed.

uid1 = 1 uid2 = 2

Is the same as:

uid1 = 2 uid2 = 1

Nope, it's not.

On Facebook, for instance, I've a number of customers who sent requests to become "friends" that I never accepted... Since they're mere acquaintances.

Along the same lines, I might have marked a few people as best friends, and they didn't reciprocate, or vice versa. Or perhaps I'm ignoring a few and they are not.

Basically, there's a lot more information in a (uid1, uid2) tuple than mere IDs.

Make sure that you never need to deal with situations like these before deciding to add e.g. a uid1 < uid2 constraint on your table.

Many-to-many relationship with different data types

The solution you have chosen is called Entity-Attribute-Value (EAV.) The most common approach is to store all values as strings. Some people add a validator column, containing a regex or like expression, that is verified by the client or t-sql function updating the value.

It is much much cleaner to use nullable columns.

---

Related Topics