Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server

On my database structure in SQL Server, I have 3 types of products which requires different information about the order. So, I created one Customers table and three different orders tables: OrdersForProductAs, OrdersForProductBs, OrdersForProductCs. All orders table has one to many relationship on Customers table.

I also have another table which is Payments and will hold the payment details inside. But I have doubts here on how to structure it.

As I have multiple product types and a customer may have orders for multiple products at the same time, I need to relate those three order tables to Payments table.

The other issue is that a customer may have an order for only one type of product. So, the FK columns on Payments table needs to be nullable.

My question is whether those nullable FK columns would be a headache for me on the long run or not? Generally speaking, would it be considered as a bad practice to have nullable FK columns on a table?

I'd question why you have OrdersForProductX tables at all

It's possible the FK problem you've asked about can be designed out...

If these tables have the same structure, then you simply need a ProductType column on some OrderProduct table. Then Payment just links to that with one FK

If the table have different structures, I assume they have some common attributes. So, you can have a common OrderProduct table then specific child table per product type (see below) Again, Payment just links to the commone table with one FK

This is the "superkey/subtype pattern"

• UQ1 is the "super key" used a foreign key on the subtype tables

• Each subtype table has a composite PK and FK on (OrderID, ProductType)

• Each subtype table has a CHECK constraint to restrict types in that table

OrderProduct

• OrderID, PK, UQ1

• ProductType, UQ1

• CommonThing1

• ...

OrderProductA

• OrderID, PK, FK

• ProductType, PK, FK, CHECK ProductType = A

• ProductAThing1

• ...

OrderProductB

• OrderID, PK, FK

• ProductType, PK, FK, CHECK ProductType = B

• ProductBThing1

• ...

StackExchange Database Administrators Q#13470, answer score: 15