If I have two tables in SQL with many relationships, 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 can obviously be attached to many items.

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

create table bla bla

for the third table?

Answer 1

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

  item_id (Foreign Key) category_id (Foreign Key) 

edit: you can consider item_id and category_id as the primary key, they uniquely identify only the entry. In some applications, I found it useful to include an additional numeric identifier for the record itself, and you can enable it if you are so inclined

Think of this table as listing all the mappings between items and categories. It is short and easy to request.

edit: remote (optional) primary key.

Answer 2

Yes, you cannot form a many-to-many relationship between two tables with these three tables. You can form one-to-many (in one of two directions), but in order to get 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 Element table unless you have any privileged category for an element that does not look here. I'm also not a big fan of introducing unnecessary primary keys when there is already a “real” unique key in the join table. The fact that item and category identifiers are already unique means that the entire entry for the ItemCategory table will also be unique.

Just control the performance of the ItemCategory table using standard tools. You may need an index for one or more of:

• Itemid
• CategoryID
• (Itemid, CategoryID)
• (CategoryID, Itemid)

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

The actual syntax for the entire job will look like this:

 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) ); 

Other things you should consider, for example, so that your identifier columns are in the identification / auto-increment columns, but not directly related to the issue in question.

Answer 3

Yes, you need a connection table. In a one-to-many relationship, objects on the many-side can have an FK link to objects on the one-side, and this is enough to define all relationships, since each of the many-objects can have one single object.

In a many-to-many relationship, this is no longer enough because you cannot fill out multiple FK links in one field. (Well, you can, but then you lose the atomicity of the data and all the nice things that come with the relational database).

Here is the connection table - for each relationship between Item and a Category relationship is represented in the connection table as a pair: Item.id x Category.id .