Sql-server – Normalizing a folder schema

schemasql server

I've created a schema to allow creation of folders for customers.

Customers

Id
Firstname
Lastname
StreetLine1
StreetLine2
City

CustomerFolders

Id
CustomerId
ParentId (NULL)
Name

Now as you can see, you can create folders with a specified name and which can be arranged like a tree structure.

My problem now is this: CustomerFolder has a relation to Customer (which is a good thing) and to its parent (which alone is also a good thing). However, it would be possible to create a folder related to a customer with ID 1 and for the same folder to have a parent folder that relates to a customer with id 2.

How can I prevent this at database level?

Best Answer

If you want to force every child of every parent to have the same customer then there are two quick solutions that you could use to accomplish this.

The first, and my preference, would be to use a check constraint on the table like so:

-- define a function that performs the logic required
CREATE FUNCTION dbo.fn_CheckCustomersAreEqual(ParentID INT, CustomerID INT)
RETURNS BIT
AS
BEGIN
RETURN CAST(ISNULL(
    SELECT 1 
      FROM dbo.CustomerFolders 
     WHERE ID = ParentID AND CustomerID = CustomerID
), 0) AS BIT);
END

-- add a check constraint to the table using the function to provide the outcome
ALTER TABLE dbo.CustomerFolders 
ADD CONSTRAINT CK_CheckCustomersAreEqual 
CHECK (dbo.fn_CheckCustomersAreEqual(ParentID, CustomerID));

The second option would be to add a trigger to the table to enforce the requirements like so:

CREATE TRIGGER CheckCustomersAreEqual ON dbo.Folders
INSTEAD OF INSERT
AS
BEGIN
-- check to see if the number of parents that meet the criteria 
-- match the number of records we are inserting
IF (SELECT COUNT(*) FROM INSERTED AS F 
                    JOIN dbo.CustomerFolders AS I ON I.ParentID = F.ID
                                      AND I.CustomerID = F.CustomerID) <>
   (SELECT COUNT(*) FROM INSERTED) 
BEGIN
-- they don't match so raise an error
RAISERROR('Child customer does not match parent customer', 16, 1);
END
ELSE
BEGIN
-- they match so insert
INSERT dbo.CustomerFolders 
SELECT *
  FROM INSERTED;
END
END

You can adjust the error handling and message returned to the client a bit better in the trigger but I personally prefer using the CHECK CONSTRAINT version.

These are just two possibilities that I can come up with, there may be more and you may be able to optimize the function or trigger to perform better. You could also change the design of your database to accommodate this better if you wanted. I just wrote the example above off the top of my head so be aware that they are also untested.

EDIT

I just noticed that the parent can be null (which makes sense) - but the example I gave above does not account for this. Make sure you account for this scenario in your end solution.

Related Question