Storing invoices in a database

Question

I am making a piece of invoicing software and I want it to save each individual invoice.

The user creates invoices by selecting a customer, as well as however many items are being billed to the customer. Seeing as most invoices will have multiple items, what is the best way to save them to the database without being incredibly redundant? I'm willing to rearrange my entire database if need be.

My tables look like this:

Customers Table:

Id        / Primary key
FullName
Address
Phone

Items Table (a table of products offered):

Id        / Primary key
ItemName
Price
Description

Invoices Table (saved invoices):

Id        / Primary key
CustId    / Foreign key is Id in Customer table
ItemId    / Foreign key is Id in Item table
Notes

Answer 1

You need another table to store invoices (what you call Invoices now actually stores invoice items).

Customer
    id
    name
    etc.

Item
    id
    name
    etc.

Invoice
    id
    cust_id
    date

InvoiceItem
    id
    inv_id
    item_id

This is the classic way of modeling a many to many relationship using a junction table (i.e. InvoiceItem).

Answer 2

It looks like you will actually want a 4th table to join them. To normalize your data, only keep on each line things that are specific to that invoice

Invoices table

Id        / Primary key
CustId    / Foreign key is Id in Customer table
Notes

Invoice Items table

InvoiceId
ItemId