How does one make a invoice database using a budget from a contract?



  • The primary key of an invoice table is not the same as the primary key of the budget table. The budget has the same exact invoice descriptions as the invoice table but there isn’t really a primary key in the budget table that is a list of things people agree to pay.

    I want to create a database that is forward looking but I can’t pre create a database of invoices as I don’t have the invoice ID until we are billed. I can create a table of items to be paid based on the contract budget but then I have to change the primary key and I don’t know what the primary key is.



  • It's not common for the primary key to match between two different tables (though there's some cases for it). Instead one table should have a second field that is the foreign key reference to the primary key of the other table, to relate the two tables together. Or sometimes both tables will relate by foreign key fields in both tables.

    Without a bit of more detail, I would guess your use case falls into the latter scenario. I would envision you have a Contracts table whose primary key field would be ContractId, Budgets table whose primary key field is BudgetId, and an Invoices table whose primary key field is InvoiceId. Both the Budgets and Invoices table would have a foreign key referencing field called ContractId as well which would reference the primary key ContractId field from the Contracts table.

    In the above scenario, this would relate all Invoices you receive for a given Contract to the correlating Budget of that contract. This assumes that your Invoices and Budgets aren't one-to-one with each other per se (e.g. a Contract can have one big Budget but is broken out into multiple Invoices). This is usually the common way budgeting and invoicing works in practice.

    But if your use case truly has a one-to-one relationship between Budgets and Invoices then all you need to do is have a foreign key referencing field called BudgetId in your Invoices table, which references the BudgetId field from your Budgets table. And you would set the Invoices.BudgetId to the correct value when the Invoice record is created.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2