Solution to Polymorphism?

Question

Solution to Polymorphism?

timwell

SSCertifiable

Points: 5315
More actions
September 5, 2017 at 1:20 pm

#339043

I am looking for a solution to one of the Code Smells listed in Phil Factor's list, (https://www.red-gate.com/simple-talk/sql/t-sql-programming/sql-code-smells/#using-a-polymorphic-association) and would be interested in how others have addressed this kind of situation.
In Phil's list it's number 1.5 about using a polymorphic association, that is, a table with a foreign key that points to different tables based on another field in the table.
I have a table with a list of scanned documents with fields for type, sub-type, and a foreign key. The scanned document can be attached with the foreign key to different forms (tables) based on the type and sub-type. Then there are tables that have the data for the forms the scans are attached to. For example the I-9 form/table will have attached documents that are scans of identification. A background check form/table will have scans of the MVR or whatever official documents. It seems like a good thing to keep the list of scanned documents in one place rather than different locations based on the type. (Or maybe not...)
Phil's article suggests an intersection table with links to both the scanned document and the form/table. In this case it seems like the intersection table would have the same problem, unless there were different intersection tables for each type. There are about 20 different document types and more may be added later.
How have others handled this kind of situation? What are some of the issues with polymorphic association?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

timwell SSCertifiable Points: 5315 More actions · Answer 1

Here is what I came up with for the polymorphic table structure:

CREATE TABLE [dbo].[ScannedDocument]( [ScanId] [int] IDENTITY(1,1) NOT NULL, [ScanFFoldNumber] [int] NULL, [ScanFileName] [varchar](260) NULL, [ScanDescription] [varchar](300) NULL) CREATE TABLE [dbo].[FileReference]( [FRefId] [int] IDENTITY(1,1) NOT NULL, [EmpId] [int] NULL, [ScanId] [int] NULL, [FFoldNumber] [int] NULL, [FDTypeId] [int] NULL, [RelatedId] [int] NULL, [IsAttachment] [int] NULL)

The document information (file name, folder location) is in Scanneddocument.
When the scan is associated with an employee and/or related table (type) a FileReference row is created. Multiple references can potentially exist for the same file.
EmpId will always point to the Employee record. RelatedId will be different based on the document type type.
The folder in ScannedDocument is the physical folder where the document is stored. The folder in FileReference is the logical folder where the user goes to find the document.

Now I will wait for the knock on the door from the Relational Police.....

x SSC-Insane Points: 23660 More actions · Answer 2

Maybe you could have filereferencei9, filereferenceMVR, etc that takes the place of the "polymorphic" filereference? Otherwise, you'll now need to inventory the types by checking explicitely by type instead of simply left joining to the appropriate "monomorphic" table?

But honestly I doubt your design as it is will beat you up too much, maybe theres some downsides to it, maybe not. On the bright side, you and Phil made me think for a bit, that doesn't happen much!

timwell SSCertifiable Points: 5315 More actions · Answer 3

Yes, I think that's what they are referring to, one many-to-many link table for each document type.
I am resisting that because it's a lot of tables, and we have to add another one if we add another document type.
Since the amount of data is so small it seems like there will not be any major delays. It might be interesting to do a comparison with millions of rows between querying from a catch-all table or from a dedicated single-type table.