Table Primary Key and Foreign Key Relationship Design for Invoices with Summary and Detailed Sections

Question

I have the following tables in my database:

Person

Person_ID (PK)
Name
Company_ID(FK)
Phone
Email

Company

Company_ID (PK)
Name
Address
City
State
Invoice_ID (FK)

Invoice

Invoice_ID (PK)
Summary_ID (FK)
Detailed_ID (FK)

Summary_Section

Summary_ID (PK)
InvoiceNumber
Date
DueDate

Detailed_Section

Detailed_ID (PK)
Person_ID (FK)
Amount
Info

Each company has multiple people and receives one invoice.

Each invoice is broken into two sections.

The Summary section shows the company name, company address, invoice number, invoice date, & due date.

The Detailed section shows the person's name, amount, & additional info.

Is this correct?

How should I go about this?

Am I assigning the foreign and primary keys correctly?

Answer 1

The PK/FK assignment seems to be ok. Although I would suggest the following:

1. If you are using any kind of framework, try to be consistent with its conventions. For example, Ruby on Rails usually calls the primary key (if it is just one column) as id for all tables. Note: column names across tables don't have to be unique.
2. Do you really need to separate the summary and detailed sections in the DB? You could combine the Invoice, Summary, and Detailed in one table, and then just retrieve/display the columns you need. This is both a design and engineering issue.
   • Design: Can two invoices ever share a section?
   • Engineering: How many times do you think the sections might be edited/retrieved?