SQL: Optional Foreign Keys

I have the following tables....

"Floor" (which has many) "Area" (which has many) "Asset",

then I have a "Task" table.

A "Task" can be assigned to either an "Area" or an "Asset"; so I have two columns in the "Task" table named areaId and assetId, one of which must have a value.

1st question: How do I query all tasks which have occurred on a given floor?

2nd question: How do I enforce referential integrity?

3rd question: Is this approach recommended? Any suggestions welcome if not.开发者_如何学运维

Many thanks for any answers given,

ETFairfax

---

1st question:

SELECT
    distinct t.TaskID
FROM
    Floor f
        inner join
    Area a
        on
            f.FloorID = a.FloorID
        left join
    Asset ast
        on
            a.AreaID = ast.AreaID
        inner join
    Task t
        on
            t.AreaID = a.AreaID or
            t.AssetID = ast.AssetID
 WHERE
     f.FloorID = @FloorID

should give you the shape of the query you're trying to build. (Of course, you don't actually need to query the Floor table if you already have a FloorID)

2nd question:

 CREATE TABLE Task (
     TaskID int not null,
     AreaID int null,
     AssetID int null,
     constraint PK_Task PRIMARY KEY (TaskID),
     constraint FK_Task_Area FOREIGN KEY (AreaID) references Area (AreaID),
     constraint FK_Task_Asset FOREIGN KEY (AssetID) references Asset (AssetID),
     constraint CK_Task_OneNonNull CHECK (
         (AreaID is null and AssetID is not null) or
         (AssetID is null and AreaID is not null))
 )

Or, alternatively, you can make AreaID not null, add a unique constraint on the Asset table across (AreaID,AssetID), and then make the foreign key to the Asset table reference both columns - this ensures that if an AssetID is supplied, it's linking to an Asset that belongs to the correct AreaID. This would also simplify the answer to Q1, if there's always an AreaID in Task

3rd question:

difficult to say without knowing how this is all going to be used. I don't think it's an un-reasonable approach.

---

Let's start with question 3, is this a valid approach? It is if it models reality. If floors, areas, and assets are unlike things, with more distinct properties than properties in common, and if the operations on them tend to be different, then your 3 table approach is fine.

For the second question, simply put foreign keys onto both areaId and assetId. Null values are ok in foreign keys.

But you need a table level constraint that will not allow both of them to be null, nor allow both of them to be populated.

Finally, having settled the issue of schema and constraints, we can do the query, which is pretty straightforward. The idea is that some tasks join through asset through area to floor, while others join through area straight to floor. For that you need to union two queries together:

-- First query is the three level
select task.*
  from task
  join asset on task.assetid = asset.id
  join area  on asset.areaid = area.id
  join floor on area.floorid = floor.id
 where floor = xxxx
UNION ALL
-- Second query is the two level
select task.*
  from task
  join area  on task.areaid  = area.id
  join floor on area.floorid = floor.id
 where floor = xxxx

This will return the correct results but may not be the fastest. Putting a WHERE clause on each subquery to filter out NULL/NOT NULL may speed it up, that you would have to investigate.