Take these tables for instance.

Item
    id
    description
    category

Category
    id
    description 

A product can fit in with many groups along with a category clearly could be mounted on many products.

Wouldso would the database be produced in cases like this? I am unsure. Someone stated produce a third table, but should i do this? Will I literally perform a

create table bla bla

for that third table?

Yes, you have to produce a third table with mappings of ids, something with posts like:

 item_id     (Foreign Key)
 category_id (Foreign Key)

edit: you are able to treat item_id and category_id like a primary key, they distinctively identify the record alone. In certain programs I have thought it was helpful to incorporate one more number identifier for that record itself, and you will optionally include one if you are like doing so

Think about this table as all of the all of the mappings between Products and Groups. It's concise, and it's not hard to query against.

edit: removed (unnecessary) primary key.

Yes, you'll need a "join table". Inside a one-to-many relationship, objects around the "many" side might have an FK mention of the objects around the "one" side, which is sufficient to look for the entire relationship, since each one of the "many" objects are only able to possess a single "one" object.

Inside a many-to-many relationship, this really is no more sufficient since you can't stuff multiple FK references in one area. (Well, you can, however you'd lose atomicity of information and every one of the excellent stuff that include a relational database).

This is when a join table is available in - for each relationship between an Item along with a Category, the relation is symbolized within the join table like a pair: Item.id x Category.id.

Yes, you can't form another-normal-form many-to-many relationship between two tables with only individuals two tables. You can form a 1-to-many (within the two directions) but to be able to obtain a true many-to-many, you'll need something similar to:

Item
    id primary key
    description

Category
    id primary key
    description

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

You don't need a category within the Item table unless of course you've some fortunate category to have an item which does not appear to be here. I am also not really a large fan of presenting unnecessary primary secrets when there's already a "real" unique key around the joining table. The truth that the product and category IDs happen to be unique implies that the whole record for that ItemCategory table is going to be unique too.

Simply monitor the performance from the ItemCategory table making use of your standard tools. You might require a catalog on a number of of:

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

with respect to the queries you utilize to become listed on the information (and among the composite indexes will be the primary key).

The particular syntax for the whole job could be like:

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 some other type of things you should look at, for example making your ID posts into identity/autoincrement posts, but that is in a roundabout way highly relevant to the question at hands.