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

database-designsql serversql-server-2008sql-server-2008-r2

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?

Best Answer

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
...

Related Solutions

Which option is better for not slow down the performance of queries

I would use a system where each payment is recorded separately

Typically, you'd want to keep the "balance" consistent over time and this requires them to be recorded individually.

the ability to link payments
record cancellations separately

I would never concatenate previous payments into one opaque string.

I also wouldn't worry about performance yet: this is premature optimisation. If you do expect 100s of millions of rows, there are other techniques to improved performance. Otherwise, concentrate on data integrity and correctness

Sql-server – Is adding rows better then adding columns in case their value is not consistent

First, it's about normalization. Today you have max 30 special products, tomorrow (or after 10 years for that matter) you might need another extra 5. You will need to write more code, but you will have greater flexibility after all. At least you won't need to code (or design) 30 columns! You can have a schema like this:

CREATE TABLE Product (ProductId...)
CREATE TABLE Customer (CustomerId...)
CREATE TABLE CustomerProductPrice (ProductId, CustomerId, Price...)

In the third table you can put price for product/customer, but remember to create a unique constraint on foreign key columns like ProductId, CustomerId.

Best Answer

Related Solutions

Which option is better for not slow down the performance of queries

Sql-server – Is adding rows better then adding columns in case their value is not consistent

Related Question