Hi all. I have a database design conundrum for you....

The Scenario

A table, called SystemDocuments_History, has a m:1 relationship with SomeSystemDocuments, and also SomeOtherSystemDocuments. To distinguide which table the history is held for, there is a "Key" column in History, which could be "S" for one document type, and "O" for the other one.

So, if you wanted all the history items for SomeSystemDocuments with ID of 2, you'd have to filter SystemDocuments_History for Document_ID = 2 and Key = 'S'.

Is this good, or should there be 2 history tables - and if so why?

With this design above, the advantages are that fewer tables is easier from a scripting side, but the relationship is not a "true" relationship in that the foreign key constaint can not be enabled. Theres also arguably less code - the code is more "abstract" and re-use might be better.

The alternative is one history table per document table. The way I see it, the advantages to that is that the relationship can be defined and enabled, and its easier to retrieve the data from the database (very straightforward).

Is one of these approaches frowned upon, which is better, from a practical viewpoint? I'd be keen to know anyones thoughts....

I think there is no correct option and wrong option. But I always go in favour of a strong relation maintained by the database, not by code. Since I always tend to design my database first, and I try not to think of implementation (development) issues when I'm designing the database, this always leave me no other option to maintain this relation.

One benefit of having it in one table is you only have to look one place to get document history. But, like you've said, you lose referential integrity for the most part.

One idea is to put them in separate tables but then build a view to pull them into one place to look for the history.

I would not hesitate to call having one table without "real" relations a bad design. I would not allow this in one of my projects (assuming it was up to me, which isn't always the case).

It sounds like you understand the pros and cons of doing it both ways, though, so you know what you are getting yourself into. Depending on the project, sometimes a less-than-ideal design is actually the best solution for a particular case.

MattWoberts wrote:

Hi all. I have a database design conundrum for you....

The Scenario

A table, called SystemDocuments_History, has a m:1 relationship with SomeSystemDocuments, and also SomeOtherSystemDocuments. To distinguide which table the history is held for, there is a "Key" column in History, which could be "S" for one document type, and "O" for the other one.

So, if you wanted all the history items for SomeSystemDocuments with ID of 2, you'd have to filter SystemDocuments_History for Document_ID = 2 and Key = 'S'.

Is this good, or should there be 2 history tables - and if so why?

With this design above, the advantages are that fewer tables is easier from a scripting side, but the relationship is not a "true" relationship in that the foreign key constaint can not be enabled. Theres also arguably less code - the code is more "abstract" and re-use might be better.

The alternative is one history table per document table. The way I see it, the advantages to that is that the relationship can be defined and enabled, and its easier to retrieve the data from the database (very straightforward).

Is one of these approaches frowned upon, which is better, from a practical viewpoint? I'd be keen to know anyones thoughts....

Hmmm...one question is whether SomeSystemDocuements and SomeOtherSystemDocuments represents different types of the same thing; i.e., can you create an inheritance hierarchy like SystemDocument->SomeSystemDocument and SystemDocument->SomeOtherSystemDocument?

If that's the case then your history table can point to the base SystemDocumentTable and allow LLBLGen's inheritance system take care of differentiatiing between the two sub-types.

Jeff...

Thanks all. I'm glad everyone seems to be in the same mindset about this - thre are pro's and cons about each one, I wanted to make sure that this wasn't a golden rule of DB design I am breaking - although I can see why generally its frowned on.

Jeff - thats very interesting what you say about inheritence. yes they do represent the same kind of thing. So SomeSystemDocument and SomeOtherSystemDocument are both speciifc implementations of a SystemDocument. The reason I never gave much thought to this is because I don't have a table in the DB that would represent the "Base class", i.e. a SystemDocument. Would I have to create a SystemDocument in the database to get the inheritence tree to work , or is something that LLBLGen can take care of?

Thanks

Matt.

I believe you'll have to create the base table, but all it needs is the PK. Although, of course there might be some shared data fields between the derivatives that can/should be pulled into the base table.

Cheers Jeff.

You inspired me to read through inheritence again, and I quite liek it - it offers some solutions to a few "issues" we have with our database

I managed to get this cenario working with inheritence without a root table. I created sub-types for the history tables that were specific to a particular type of document - interesting stuff.

Gotta looooove LLBLGen.

Jeff...