SQL - searching for values that could be created in the future

Question

I have a table Templates that has Type, ID (pk) and Name columns. These templates can be applied to two different tables, Person and Role. Users can create their own templates, but these will always be of a pre-defined type.

I have written code that checks the usage levels of currently existing templates, however adding in functionality to check for templates that may be created in the future is beyond my current capability.

SELECT t.Name As [Template Name],
  CASE
    WHEN t.Type = 5 THEN SUM(CASE WHEN p.tID BETWEEN 2 AND 3 THEN 1 ELSE 0 END)
    WHEN t.TYPE = 10 THEN SUM(CASE WHEN r.tID BETWEEN 4 AND 5 THEN 1 ELSE 0 END)
    WHEN t.TYPE = 100 THEN SUM(CASE WHEN p.tID = 8 THEN 1 ELSE 0 END)
    WHEN t.TYPE = 20 THEN SUM(CASE WHEN r.tID BETWEEN 6 AND 7 THEN 1 ELSE 0 END)
    WHEN t.TYPE = 3 THEN SUM(CASE WHEN r.tID BETWEEN 9 AND 10 THEN 1 ELSE 0 END)
  END As [Times Used],
  t.ID As [Template ID], t.Type As [Template Type]
  FROM Templates t, Person p, Role r
  GROUP BY t.Name, t.ID, t,Type
  ORDER BY [Times Used] ASC, t.Type, t.Name, t.ID

...The five types above are constant. As above, I would like the code to be able to check for additional templates that may be added in the future but am not sure how to do this.

As always, any help gratefully received.

Am using SQL Server 2012.

M

p.s. Just as a quick note to everyone here, I inherited this database I have no control over the existing data structure. I am trying to future-proof this as best I can for others.

To clarify, the Template.Type values will never change. However a user could create a new Template (templates can only be applied to a Person or a Role, never both) at any point in the future, which would be of one of the above five types.

For Template.Type, values 5 & 100 indicate that the Template is for a Person. Values 10, 20 & 3 indicate that it is for a Role. As such in those instances only the Person and Role tables respectively will need to be searched through.

My code currently searches for all existing Template.ID values, but I am not sure how to add in functionality to search for ID values that may be created in the future.

Answer 1

You could just add an ELSE clause to your CASE statement, which would catch anything that isn't accounted for:

 CASE
    WHEN t.Type = 5 THEN SUM(CASE WHEN p.tID BETWEEN 2 AND 3 THEN 1 ELSE 0 END)
    WHEN t.TYPE = 10 THEN SUM(CASE WHEN r.tID BETWEEN 4 AND 5 THEN 1 ELSE 0 END)
    WHEN t.TYPE = 100 THEN SUM(CASE WHEN p.tID = 8 THEN 1 ELSE 0 END)
    WHEN t.TYPE = 20 THEN SUM(CASE WHEN r.tID BETWEEN 6 AND 7 THEN 1 ELSE 0 END)
    WHEN t.TYPE = 3 THEN SUM(CASE WHEN r.tID BETWEEN 9 AND 10 THEN 1 ELSE 0 END)
    ELSE SUM(CASE WHEN r.tID > 10 THEN 1 ELSE 0 END)
  END As [Times Used]

r.tID seems to go up to 10 in your cases, so I'd assume new data to have values greater than 10. With the ELSE this would simply pick up any TYPE values that aren't accounted for in your CASE WHEN clauses. If there is other logic involved, you may need to modify the above.

Answer 2

Your data structure is not obvious to outsiders, and won't be obvious to your successors as well, making maintenance harder than necessary. Normally, you would make two tables, one for Person templates, and one for Role templates.

That said, it will also work with one table. When trying to find how often a certain row is linked from outside tables, you should use a join on the tables and count the resulting lines:

SELECT t.ID,t.Name As [Template Name],COUNT(t.ID) AS [Times used]
FROM Templates t
LEFT JOIN Person p ON(t.ID == p.tID)
LEFT JOIN Role r ON(t.ID == r.tID)
WHERE p.tID IS NOT NULL OR r.tID IS NOT NULL
GROUP BY t.ID,t.Name

This will obviously overestimate when a database inconsistency causes a template to be referenced in both Person and Role table. One could certainly work around that, but then, I don't think that your data structure is worth any more effort...

Answer 3

My answer shows adding foreign keys to Person and Role tables, then using a simple CTE query to get your totals:

SQL Fiddle

MS SQL Server 2014 Schema Setup:

CREATE TABLE Template
(
  ID INTEGER PRIMARY KEY,
  NAME VARCHAR(50),
  Type SMALLINT
 )

 CREATE TABLE Person
 (
   ID INTEGER IDENTITY  PRIMARY KEY,
   TID INTEGER,
   Name VARCHAR(50)
  )


  CREATE TABLE Role
  (
   ID INTEGER IDENTITY  PRIMARY KEY,
   TID INTEGER,
   Name VARCHAR(50)
  )

  ALTER TABLE Person
     ADD CONSTRAINT FK_Person_Template
     FOREIGN KEY (TID)
    REFERENCES Template(ID)

  CREATE INDEX IX_Person_TID ON Person(TID)

  ALTER TABLE Role
     ADD CONSTRAINT FK_Role_Template
     FOREIGN KEY (TID)
    REFERENCES Template(ID)

  CREATE INDEX IX_Role_TID ON Role(TID)

  INSERT INTO Template 
  VALUES  (1, 'Type 5', 5)
        , (2, 'Type Ten', 10)
        , (3, 'Type One Hundred', 100)
        , (4, 'Type Twenty', 20)
        , (5, 'Type Three', 3)

 INSERT INTO Person (TID, Name)
 VALUES   (1, 'Fred')
        , (1,'Mary')
        , (3,'John')
        , (5, 'Marty')
        , (5, 'Sharon')
        , (5, 'Jules')
        , (5, 'Maxine')

  INSERT INTO Role (TID, Name)
 VALUES (2, 'Asministrator'), (2,'Developer')
      , (4,'MailRoom'), (4, 'Posty'), (4, 'PA'), (4, 'HouseKeeper'), (5, 'Front Desk')

Query 1:

;WITH CTE
AS
(
  SELECT Tid
  FROM Person
  UNION ALL
  SELECT TId
  FROM Role
)
SELECT T.Name As [Template Name], COUNT(*) AS [Times Used],
       MAX(T.Id) As [Template Id],
       MAX(T.Type) As [Template Type]
FROM Template T
INNER JOIN CTE C
   ON T.Id = C.TId
GROUP BY T.Name
ORDER BY T.Name

Results:

|    Template Name | Times Used | Template Id | Template Type |
|------------------|------------|-------------|---------------|
|           Type 5 |          2 |           1 |             5 |
| Type One Hundred |          1 |           3 |           100 |
|         Type Ten |          2 |           2 |            10 |
|       Type Three |          5 |           5 |             3 |
|      Type Twenty |          4 |           4 |            20 |

Query 2:

-- ADD A NEW TEMPLATE
INSERT INTO Template 
  VALUES  (6, 'Type Two Hundred', 200)

-- INSERT NEW PERSON with this template
INSERT INTO Person (TID, Name)
 VALUES   (6, 'Malky')

Results: Query 3:

 -- RERUN QUERY
 ;WITH CTE
AS
(
  SELECT Tid
  FROM Person
  UNION ALL
  SELECT TId
  FROM Role
)
SELECT T.Name As [Template Name], COUNT(*) AS [Times Used],
       MAX(T.Id) As [Template Id],
       MAX(T.Type) As [Template Type]
FROM Template T
INNER JOIN CTE C
   ON T.Id = C.TId
GROUP BY T.Name
ORDER BY T.Name

Results:

|    Template Name | Times Used | Template Id | Template Type |
|------------------|------------|-------------|---------------|
|           Type 5 |          2 |           1 |             5 |
| Type One Hundred |          1 |           3 |           100 |
|         Type Ten |          2 |           2 |            10 |
|       Type Three |          5 |           5 |             3 |
|      Type Twenty |          4 |           4 |            20 |
| Type Two Hundred |          1 |           6 |           200 |