Multiple foreign keys, only one can be NOT NULL

[Thomasso]

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

[Thomasso]

And a similar problems - I am storing contact persons from the customer side and the supplier side. But I would like to keep all people in a single table...

So, can I do this?
PersonID
CustomerID
SupplierID

(one of which will be NULL).

[CJ_London]

contact persons from the customer side and the supplier side.

easiest thing to do is to combine suppliers and customers into one table as well with a flag to indicate which.

Any invoiced item can be either from a Sales Order, a Service Order, or a Repair Order.

since your design does not allow maintaining referential integrity, have one FK field and as above another field to indicate the type of order

Not sure why you are subdividing orders into different tables - would be simpler to have the one table and perhaps a few redundant fields depending on order type. Long term you are creating a problem for yourself if you want a view of 'all orders' - you'll need to use a union query, perhaps something more complex which ultimately negates the use of indexes resulting in slow performance

Also means you need to maintain 3 orders forms, not 1. OK form might be a bit more complex because of the need to hide/show fields relevant to the order type, but at least everything is in one place

[Thomasso]

Hi,

I have different tables because the the nature of those respective order types is completely different...

Sales Orders are a typical goods/services movement (order from supplier > come to stock > ship to customer).

Then I have a division for devices calibration service. That's a service where I need to insert calibration results, generate a calibration certificate and assign with CalibrationDetailID.
Then I deal with RMAs, where I need to fill a repair report, assign spare parts that we used, bill our work and so on.

It would be a complete mess if I tried to merge all of those things into a single table.

Basically there is a completely different work flow with those things, therefore different fields, different forms, different reports... The only thing that they share is that they can be invoiced together.


... So back to my original question... Can this be done?

[CJ_London]

I’ve already explained how

edit: but if you want to stick with your three FK's then in a form use code in each controls after update event to make the others null and possibly disable those controls

e.g. in the SalesOrderDetailID control after update event

ServiceOrderDetailID=null
RepairOrderDetailID=null
ServiceOrderDetailID.enabled=false
RepairOrderDetailID.enabled=false

There is no way to do it in a table or query

[Thomasso]

I’ve already explained how

You said that I should merge orders into a single table, I replied that this is not possible nor convenient in my setup. Well, maybe someone else will reply.

[CJ_London]

You said that I should merge orders into a single table,

I advised that would be a better way to go - easier in the long run

What I offered as a solution

since your design does not allow maintaining referential integrity, have one FK field and as above another field to indicate the type of order