Hi,
I am currently working out a solution for my invoicing. I want to group invoiced items by project (or sales order or whatever) because I can invoice multiple orders at once.
The problem is that I have more types of projects (Sales Orders, Service Orders, Repair Orders) - all with their own tables and different IDs.
Is it a good idea to do it like this?
tbl1InvoiceDetails:
- InvoiceDetailID
- InvoiceID
- SalesOrderDetailID
- ServiceOrderDetailID
- RepairOrderDetailID
- ProductID
- Qty
- UnitPrice
Any invoiced item can be either from a Sales Order, a Service Order, or a Repair Order. So the idea is that I select some item from Sales Orders and the SalesOrderID will be assigned. However, I want the database to disallow assigning that item to Service or Repair.
In other words, two of those 3 foreign keys will always be NULL.
I am using SQL Server 2019 Express.
Is this possible? And is it a good idea?
Thanks a lot.
Tomas