OO behaviour for an ER database (One-to-many relationship for multiple tables)

Question

I'm designing a document management system for a school (SQL & .NET). The school has various data it would like to store, such as personnel, pupils, financials etc. The system should be able to attach a document to any of the records in the system irrespective of its type (personnel, pupil...)

A typical ER way of doing this would be to create tye different type tables (personnel...) as well as a Documents table. To associate the type table records to the document records, one would need a "link table" such as PersonnelDocuments, with the columns PersonnelID and DocumentID.

I find this approach to be a bit clunky as there might be 100s of type tables (in future systems) who would each need a linking table. Is there a more generic way of doing it, while still following proper ER DB design.

Answer 1

One solution is to make your unique identifier so big that that every object in the database has a unique identifier regardless of the table it is in (GUID is often just that). Once you have a unique identifier for every object, you only need a single link table. I have used this approach for an application which required you to be able to post comments on any object in the database, and just as you did, I found that dozens of tables named XXXXComments and YYYYComments just was not going to cut it. This solution cleaned things up nicely.

EDIT: Yet another approach I have used in the past is to make all the tables that require a documents link derive from the same base table. This approach is often quite clunky in itself however unless your domain is rather small, or the section requiring the documents link is self contained. If I remember correctly, I was creating a reports tracking application in which all there were many types of reports, each of which could be forwarded to different groups in the company, then were returned and forwarded to other groups until all necessary items were acquired for processing. I ended up creating a report base table and my link table for forwards used that Id instead of creating a forwards table for each type of report.

Answer 2

You describe the system as a document management system. One solution then would be to honour the primacy of the documented nature of the entities in the system. The following model defines an abstract entity which acts as the parent for a table of documents and as the super-type for a bunch of sub-type tables. This model allows you to associate any number of documents with a DocumentedThing.

DocumentedThing
---------------
ThingId      Integer primary key
ThingType    String check (ThingType in ('PUPIL', 'STAFF'))
             unique key (ThingId, ThingType)

Document
--------
ThingId      Integer foreign key references DocumentedThing (ThingId)
DocumentId   Integer primary key
Text         Clob 

Pupil
-----
PupilId      Integer primary key
ThingId      Integer unique key
ThingType    String check (ThingType = 'PUPIL')
Name         String
DateOfBirth  Date
             foreign key (ThingId, ThingType) 
                 references DocumentedThing (ThingId, ThingType)

Staff
-----
StaffId      Integer primary key
ThingId      Integer unique key
ThingType    String check (ThingType = 'STAFF')
Name         String
HireDate     Date
             foreign key (ThingId, ThingType) 
                 references DocumentedThing (ThingId, ThingType)

The scaffolding of unique keys ensures that a DocumentedThing can have a record in either Staff or Pupil but not both. Furthermore there cannot be multiple records in whichever sub-type table. A record in Staff can only map to a record in DocumentedThing which has a value of 'STAFF' in ThingType. The one thing which is hard to enforce is the insistence that a DocumentedThing must have a sub-type record. It can be done, but it is usually complicated, and to some extent depends on your choice of DBMS product.

Adding a new sub-type has a minimal impact: besides adding the actual table to hold its information you only need to add a new value to DocumentedThing.ThingType; if you have a lot of sub-types you might choose to restrict this with a foreign key to a look-up table rather than a check constraint.