Associating notes with many different entities

[denis_v]

I would like to use an Access database to keep track of issues related to the design of a new manufacturing plant.

My model of a plant starts with a single Plant object, which can have many Areas, each of which can have many Machines, which in turn can have many Components.

I'd like to associate Issues with each of these four types of entities.

Then I'd like to view all of the Issues related to a particular entity. For example, if I want to view all of the Issues related to a particular Area, I'd like to see not only the Issues related to that Area, but also to the Machines and Components in that Area.

Finally, I'd like to move an Issue from one entity to another - such as from an Area to a Machine in that Area.

What would be the best way to achieve these goals?

[Phil Weber]

Hi, Denis: You could create the following tables:

Table: EntityType
-------------------------
ID           AutoNumber
Description  Text

Table: Entity
-------------------------
ID           AutoNumber
Description  Text
TypeID       Long Integer
ParentID     Long Integer

Table: Issue
-------------------------
ID           AutoNumber
Description  Memo
EntityID     Long Integer

EntityType just contains a list of types with associated IDs:

ID  Description
-------------------------
 1  Plant
 2  Area
 3  Machine
 4  Component

Entity contains the list of entities. Plants would have a null ParentID; otherwise, the ParentID would refer to the Entity ID of that item's parent, e.g.:

ID  Description     TypeID  ParentID
------------------------------------
 1  Plant 1            1      null
 2  Plant 2            1      null
 3  Area 1             2        1
 4  Machine 1          3        3
 5  Component 1        4        4

Finally, the Issue table would contain a list of issues associated with an EntityID:

ID  Description     EntityID
----------------------------
 1  Issue 1            1
 2  Issue 2            1
 3  Issue 3            3
 4  Issue 4            5
 5  Issue 5            2

To move an issue from one entity to another, you may simply update its EntityID.

The only tricky part is getting all the issues for an entity and its sub-entities. First, create a query that returns a list of all the sub-entities for a given entity ID. Here's how I've done it in SQL Server; I'll leave translating it to Access as an exercise for the reader... ;-)

CREATE FUNCTION GetSubcategories
(
	@category int
)
RETURNS 
	@subcats table
	(
		id int NOT NULL, 
		lvl int NOT NULL
	)
AS
	BEGIN
		DECLARE @lvl AS int
		SELECT @lvl = 0

		INSERT INTO @subcats
			SELECT id, @lvl FROM Category WHERE id = @category

		WHILE @@ROWCOUNT > 0
			BEGIN
				SET @lvl = @lvl + 1
				INSERT INTO @subcats
					SELECT C.id, @lvl FROM Category C JOIN @subcats S
						ON C.ParentID = S.id AND S.lvl = @lvl - 1
			END  
		RETURN
	END

Now use this function to select all issues for a given entity and its sub-entities:

SELECT ID, Description
FROM Issue
WHERE ID IN 
  (SELECT ID FROM GetSubcategories(EntityID))

Hope that helps!