Deal with many to many relationships in the same table

Question

I have a table of business names and info about them, including an address column. A business name can have duplicate names and an address can have duplicates, but both the Business name and address cannot be duplicates. So there's a many to many relationship of sorts, but that is not between two tables, it's between two columns on the same table.

One or both of these fields also need to be a foreign key of another table.

Do I create a bridge table? Composite keys? If composite keys, how would I make them foreign keys in another table?

Answer 1

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.