Search code examples
sqlmany-to-manyerd

Many-to-many SQL relationship

I have a table that currently holds both order and invoice but i believe these should be separated into 2 separate tables.

 Order       Invoice
 141           428
 141           428
 141           428
 141           646
 141           646
 222           428
 222           428

I was looking for help on the correct way to remove the many to many relationship in this example

enter image description here

Solution

  • 1) Your example shows one order having multiple invoices, but not necessarily one invoice belonging to the same order. a) If an invoice only belongs to one order, you should make a field "order" (or any other relative name you prefer) in the table "invoice", and make this a foreign key. b) If an invoice can belong to many orders (this would truly be a many-to-many relationship), you should make a third table named something like "invoiceorder", which will only have two fields, order and invoice, both of which will be the foreign key to order and invoice tables respectively.

    2) Your example also shows invoice-order tuples used many times. You will have to shed some light on this. Maybe if you use option 1b above, you could add an id and then have in your (large?) table a field which would be a foreign key to this id.