If I have two tables in SQL with a many-many relationship, do I need to create an additional table?

Question

Take these tables for example.

Item
    id
    description
    category

Category
    id
    description 

An item can belong to many categories and a category obviously can be attached to many items.

How would the database be created in this situation? I'm not sure. Someone said create a third table, but do I need to do that? Do I literally do a

create table bla bla

for the third table?

Answer 1

Yes, you need to create a third table with mappings of ids, something with columns like:

 item_id     (Foreign Key)
 category_id (Foreign Key)

edit: you can treat item_id and category_id as a primary key, they uniquely identify the record alone. In some applications I've found it useful to include an additional numeric identifier for the record itself, and you might optionally include one if you're so inclined

Think of this table as a listing of all the mappings between Items and Categories. It's concise, and it's easy to query against.

edit: removed (unnecessary) primary key.

Answer 2

Yes, you cannot form a third-normal-form many-to-many relationship between two tables with just those two tables. You can form a one-to-many (in one of the two directions) but in order to get a true many-to-many, you need something like:

Item
    id primary key
    description

Category
    id primary key
    description

ItemCategory
    itemid     foreign key references Item(id)
    categoryid foreign key references Category(id)

You do not need a category in the Item table unless you have some privileged category for an item which doesn't seem to be the case here. I'm also not a big fan of introducing unnecessary primary keys when there is already a "real" unique key on the joining table. The fact that the item and category IDs are already unique means that the entire record for the ItemCategory table will be unique as well.

Simply monitor the performance of the ItemCategory table using your standard tools. You may require an index on one or more of:

• itemid
• categoryid
• (itemid,categoryid)
• (categoryid,itemid)

depending on the queries you use to join the data (and one of the composite indexes would be the primary key).

The actual syntax for the entire job would be along the lines of:

create table Item (
    id            integer       not null primary key,
    description   varchar(50)
);
create table Category (
    id            integer       not null primary key,
    description   varchar(50)
);
create table ItemCategory (
    itemid        integer       references Item(id),
    categoryid    integer       references Category(id),
    primary key   (itemid,categoryid)
);

There's other sorts of things you should consider, such as making your ID columns into identity/autoincrement columns, but that's not directly relevant to the question at hand.