I have been working on a project at work where it should be possible to set up what suppliers are available to our customers and their users. We also wanted to be able to be able to provide the customers with a standard setup (sort of a template) based on what type of product they are using.
Here is the solution I came up with.
First the tables we already have available:
Customer
| Column Name | Value |
| CustomerId | Int (PK) |
| CustomerName | Varchar(30) |
Office
| Column Name | Value |
| OfficeId | Int (PK) |
| CustomerId | Int (FK) |
| SupplierTemplateId | Int (FK) |
| OfficeName | Varchar(30) |
Supplier
| Column Name | Value |
| SupplierId | Int (PK) |
| SupplierName | Varchar(30) |
So then we will need to add a table to hold the templates:
SupplierTemplate
| Column Name | Value |
| SupplierTemplateId | Int (PK) |
| SupplierTemplateName | Varchar(30) |
Last but not least we will need a table to hold the actual supplier selection:
SupplierSetting
| Column Name | Value |
| SupplierSettingId | Int (PK) |
| SupplierId | Int (FK) |
| SupplierTemplateId | Int (FK) NULL |
| CustomerId | Int (FK) NULL |
| OfficeId | Int (FK) NULL |
| Enabled | Bit |
| Priority | Int |
A setting can either belong to a template, a customer, a registration number or none (which makes it a default setting).
The priority is assigned as follows:
| Condition | Priority |
| No owner | 1 |
| Owned by a template | 2 |
| Owned by a customer | 3 |
| Owned by a registration | 4 |
So now we can fill in some data:
Customer
| CustomerId | CustomerName |
| 1 | Test Customer |
SupplierTemplate
| SupplierTemplateId | SupplierTemplateName |
| 1 | Template 1 |
Office
| OfficeId | CustomerId | SupplierTemplateId | OfficeName |
| 1 | 1 | 1 | Test Office |
Supplier
| SupplierId | SupplierName |
| 1 | Test Supplier 1 |
| 2 | Test Supplier 2 |
Now all that is left is set up the suppliers:
SupplierSetting
| SupplierSettingId | SupplierId | SupplierTemplateId | CustomerId | OfficeId | Enabled | Priority |
| 1 | 1 | NULL | NULL | NULL | true | 1 |
| 2 | 2 | NULL | NULL | NULL | false | 1 |
| 3 | 2 | 1 | NULL | NULL | true | 2 |
| 4 | 1 | NULL | 1 | NULL | false | 3 |
Here is a textual representation of the above data:
- On a global (default) level 'Test Supplier 1' is turned on and 'Test Supplier 2' is turned off.
- On 'Test Customer' level 'Test Supplier 2' is turned on'
- On 'Test Office' level 'Test Supplier 1' is turned off.
So how do we get this data out so we only have one row per supplier with the correct status? Well, the answer is a SELECT statement with a sub-SELECT to find the highest priority for the current supplier. Enjoy!
Here it is:
DECLARE @OfficeId INT
SELECT @OfficeId = 1
DECLARE @CustomerId INT
DECLARE @SupplierTemplateId INT
SELECT
@CustomerId = CustomerId,
@SupplierTemplateId = SupplierTemplateId
FROM
Office
WHERE
OfficeId = @OfficeId
SELECT
*
FROM
SupplierSetting s1
WHERE
(CustomerId = @CustomerId OR CustomerId IS NULL) AND
(SupplierTemplateId = @SupplierTemplateId OR SupplierTemplateId IS NULL) AND
(OfficeId = @OfficeId OR OfficeId IS NULL) AND
Priority = (
SELECT
MAX(Priority)
FROM
SupplierSetting s2
WHERE
s1.SupplierId = s2.SupplierId AND
(CustomerId = @CustomerId OR CustomerId IS NULL) AND
(SupplierTemplateId = @SupplierTemplateId OR SupplierTemplateId IS NULL) AND
(OfficeId = @OfficeId OR OfficeId IS NULL)
)
