Creating Association between two entries in SQL table

Question

I have a piece of work to do setting up a database for a small enterprise.

Currently I have 5 tables set up:

• Customers
• Accounts
• Associations
• Security(Collateral)
• References (Reference Codes relating to a Job type)

One of the tasks I have is to create an association table that will link to the Customers table and show the association between 2 customers.

Columns for Association table:

• AssociationID
• Customer1
• AssociationType
• Customer2

The output should be "Customer1 is AssocationType of Customer2" eg "Dave is Accountant for Jim"

How do I set it up so that Customer1 & Customer2 are from the Customer's table? I think it might be with Foreign Keys but I am unsure.

Answer 1

You can set up foreign keys:

alter table associations add constraint fk_associations_customer1
    foreign key (customer1_id) references customers (customer_id);

alter table associations add constraint fk_associations_customer2
    foreign key (customer2_id) references customers (customer_id);

Foreign keys should be made to the primary key, so you need to define customers so:

create table customers (
    customer_id int primary key, -- perhaps identity, serial or autoincrement depending on your database
    . . .
);

You'll note the naming conventions:

• Tables are in the plural (the contain multiple examples of something).
• The primary key is the singular followed by _id.
• The foreign key is either the same name as, or very similar to, the primary key of the referenced table.