Invoice tracking database design question

[Keith Meier, MCSA]

I'm creating a database showing who is required to review an invoice. I've
created the following tables:

Vendor
- Vendor_Name, text
- Vendor ID, number (primary key)

Approver
- Last_Name, text
- Approver_ID, number (PK)
- Approval_amount, number

Invoice:
- VendorID, number (from Vendor table)
- Invoice_Amount, number
** - Approver, number (pulled from approver table)
- other invoice information, various data types

I'll need up to 3 approver fields depending on the invoice amount. Do I need
3 of the ** fields or is there a way I can only have one and use a query or
some other tool to show which approvers needed for an invoice?

 

[jahoobob via AccessMonster.com]

Take out Approver from and add InvoiceID autonumber PK to the invoice table
and create an approval table
Approval
- InvoiceID FK
-ApproverID FK
This data would be entered in a subform of the Invoice table form linked by
InvoiceID.
You can enter as many Approvers as you want for any Invoice.

 

[Keith Meier, MCSA]

Instead of an autonumber PK I'd like to use the vendorID and invoice numbers
as PKs since they should be unique (modified my question below)

Would the Approval table look like this?
Approval
- VendorID (FK)
- Invoice_Number (FK)
- ApproverID (FK)

 

[jahoobob via AccessMonster.com]

You don't need the VendorID in the Approval table since it is already in the
Invoice table and is associated with each approval through the Invoice_Number.
Store it once rather than up to four times (once in the Invoice and once each
for three approvers.) The Invoice_Number as you have presented it is
sufficient.
Eventhough a PK is unique, doesn't mean that because something is unique that
it should be a PK. Besides, the VendorID is only unique to the Vendor table,
not the Invoice table as you can have more than one invoice with the same
VendorID.

Instead of an autonumber PK I'd like to use the vendorID and invoice numbers
as PKs since they should be unique (modified my question below)

Would the Approval table look like this?
Approval
- VendorID (FK)
- Invoice_Number (FK)
- ApproverID (FK)

Take out Approver from and add InvoiceID autonumber PK to the invoice table
and create an approval table

[quoted text clipped - 26 lines]

 

[Keith Meier, MCSA]

I have both of these as PKs because different VendorID may use the same
Invoice_Numbers, and I want to make sure that a vendor doesn't submit
duplicate invoices.

I've changed the Approval table to:
Approval
- Invoice_Number FK
- Approver_ID FK

Eventhough a PK is unique, doesn't mean that because something is unique that
it should be a PK. Besides, the VendorID is only unique to the Vendor table,
not the Invoice table as you can have more than one invoice with the same
VendorID.

Instead of an autonumber PK I'd like to use the vendorID and invoice numbers
as PKs since they should be unique (modified my question below)

Would the Approval table look like this?
Approval
- VendorID (FK)
- Invoice_Number (FK)
- ApproverID (FK)

Take out Approver from and add InvoiceID autonumber PK to the invoice table
and create an approval table

[quoted text clipped - 26 lines]

3 of the ** fields or is there a way I can only have one and use a query or
some other tool to show which approvers needed for an invoice?