Relationship between ORDER and PRODUCT

[StargazeR82]

This is more of a database design question I suppose. I have two tables...one called ORDER and one called PRODUCT. The relationship between these two is many to many. I was wondering how to connect the tables so that an instance of ORDER can contain multiple PRODUCTs. I can't think of a very efficient way to do this. Any help is greatly appreciated.

 

[swampBoogie]

1) rename the table order to something else as order is a reserved word in SQL.

2) create an additional table (e.g. orderLine) to hold information specific orders. e.g.

create table orders(OpkColumn varchar(12) primary key, otherColumns)
-- or whatever you have defined as primary key

create table products(ppkColumn varchar(10) primary key, otherColumns)

create table orderLine(OpkColumn varchar(12) references
orders, ppkColumn varchar(10) references products,
primary key (OpkColumn,ppkColumn))