Styjun

Consider the following tables for creating a simple order:

Customer(CustomerId, Name)
Order(OrderId, CustomerId)
OrderLine(OrderLineId, OrderId, CatalogItemId)

The above tables are typical. However, the following is not typical because it includes a CustomerId in it:

CatalogItem(CatalogItemId, Description, CustomerId)

In other words, each catalog item is for one client only. However, what is actually needed is that a catalog item is either assigned to a specific customer, or ANY customer.

What is the best way to model that? (It may be as simple as allowing CustomerId to be null, but I am uncertain and that is not the way it is being done currently.)

This would then be used when showing the list of CatalogItems available for a customer:

 select * from CatalogItems ci where (ci.customerId is null or ci.Customerid = @customerid);

The current system uses 0 for the CustomerId to flag that it is available to all customers, but that seems non-standard to me.

I believe these designs are fine. The important thing is that once you choose a design to stick with it. In the database I work out of daily it employs many different designs. Sometimes there are zeros, nulls, or 1s or -1s to represent that an object is for ALL entities or for no entities. Perhaps there is another meaning that is inferred that simply depends on how the database table was originally implemented and whether or not the data being populated into that table adheres to the original design.

So I say that 'Yes' - it's certainly ok.

The design you mentioned in your question is how my application determines a clients defaults. This is used in many different sections of my application.

For example - To show the system's default 'Catalog Items' available to all clients:

SELECT * FROM CatalogItems ci WHERE ci.customerId is null;

To locate custom 'Catalog Items' for a specific client:

SELECT * FROM CatalogItems ci WHERE ci.customerId = @customerid;

And as you pointed out if you want to find ALL available 'Catalog Items' for a client:

SELECT * FROM CatalogItems ci WHERE ci.customerId is null or ci.Customerid = @customerid;