Multi-Entity Document Archiving Data Model

[Agops Solutions]

Hi All,

I'm working on a database for a back-office admin system. One of the requirements is that all documents going out (and all those coming back in with signatures, etc on) need to be archived in such a way that documents for any entity can be easily retrieved.

I've googled around, revised my database normalisation and checked out the link given at the top of this forum, but I can't find anything that quite matches what I'm doing (although I'm sure it's a common problem).

To save boring you with the intricacies of the industry I'm working in, lets say this is a DB for an events management agency and has a structure similar to the one outlined below:

Agent-||---o<Artist-||---o<Booking

I then have a table linking documents for a given entity to the location of the document in the filesystem, which looks something like the following

Documents
ID
Timestamp
Template ID
Foreign Table
Foreign Key
File Path

If I want to find all the documents relating to a given booking, I can then look up all records in the Documents table with [Foreign Table] = 'Booking' and [Foreign Key] = [Booking].[ID].

Also, say the company gets an FOI request from an agent, it's fairly simple to retrieve all the documents relating to the agent, all of their artists and all of those artists' bookings.

If the company tells me that they want to start generating and archiving documents related to a Venues table, for example, I can integrate this minimal code and no change to the data-structure.

This is implemented and working well at the moment and is better than a few alternatives I thought through, but it's not a model I've seen elsewhere which worries me and somehow it just doesn't 'feel' quite right. I don't want it coming back to bite me in a couple of years when a situation I haven't anticipated comes up. So I guess my questions are:

- Is there a better/more conventional way of doing this?
- Are there any problems this model might cause?
- Does this violate any normal forms?
- If this does violate any normal forms, is it one of those rare situations where technically poor normalisation actually results in better implementation?

I'm about to implement some new features which relate to this structure and may be implementing some other features based on a similar structure, which is why I'm revisiting it now.

Many thanks for any help with this

 

[Lightwave]

From my interpretation of this model you are using a form of a Junction table.

Within the Documents Table you have the important file path and name but you also have all the Foreign Key's that you may wish to do sorts on.

So you have
AuthorID
VenueID
BookingID

At somepoint in the future you may wish to do sorts on each of these individually or a combination.

You need to ensure that you have all the foreignKeys in the Documents Table you think you are reasonably likely to be asked about.

and I don't see anything wrong with your structure and it is used very regularly you maybe haven't come across the term "Junction Table"

Which is a term used specifically to describe a table linking Foreign Keys in a many to many realtionship sort of way.

 

[Agops Solutions]

Hi Lightwave,

Thanks for your response. Reading back over my original post, I can see I've been a little ambiguous. I don't think this table quite fits the template of a classic junction table, as I understand it. It's more that the document table is in a many-to-one relationship with each of several other tables.

For example, although an Artist can have many Documents, each Document relates only to one Artist, or one Agent, or one Booking. And although a Document relating to an Artist has a relationship with that Artist's Agent, the relationship is defined only by the relationship between the artist and agent, not directly between the Document and the Agent.

I did consider a structure similar to the one you suggest and it certainly seems more like the textbook approach, but there would be a few complications for this particular implementation:

- I don't know how many types of entities I will ultimately need to store documents for, so I could potentially end up with, say, 10 foreign keys on the table, which would be a maintenance (and possibly performance?) issue.
- Doing a lookup from a Document to find its parent would get complicated, as I'd have to work out which ID was populated first.
- Not as immediately important, but reporting and analysis could be problematic for the same reason as given above.

Does that make sense?

 

[Lightwave]

Agops - sounds like you have a handle on it. I think you are going about it in the right fashion.

 

[Agops Solutions]

Thanks Lightwave,

Good to get a second opinion