What do you think about having a related translation table for each translatable table?
CREATE TABLE T_PRODUCT (pr_id int, PRICE NUMBER(18, 2))
CREATE TABLE T_PRODUCT_tr (pr_id INT FK, languagecode varchar, pr_name text, pr_descr text)
This way if you have multiple translatable column it would only require a single join to get it + since you are not autogenerating a translationid it may be easier to import items together with their related translations.
The negative side of this is that if you have a complex language fallback mechanism you may need to implement that for each translation table - if you are relying on some stored procedure to do that. If you do that from the app this will probably not be a problem.
Let me know what you think - I am also about to make a decision on this for our next application.
So far we have used your 3rd type.
One method that is used by a few wiki platforms is to separate the identifying data and the content you're auditing. It adds complexity, but you end up with an audit trail of complete records, not just listings of fields that were edited that you then have to mash up to give the user an idea of what the old record looked like.
So for example, if you had a table called Opportunities to track sales deals, you would actually create two separate tables:
Opportunities
Opportunities_Content (or something like that)
The Opportunities table would have information you'd use to uniquely identify the record and would house the primary key you'd reference for your foreign key relationships. The Opportunities_Content table would hold all the fields your users can change and for which you'd like to keep an audit trail. Each record in the Content table would include its own PK and the modified-by and modified-date data. The Opportunities table would include a reference to the current version as well as information on when the main record was originally created and by whom.
Here's a simple example:
CREATE TABLE dbo.Page(
ID int PRIMARY KEY,
Name nvarchar(200) NOT NULL,
CreatedByName nvarchar(100) NOT NULL,
CurrentRevision int NOT NULL,
CreatedDateTime datetime NOT NULL
And the contents:
CREATE TABLE dbo.PageContent(
PageID int NOT NULL,
Revision int NOT NULL,
Title nvarchar(200) NOT NULL,
User nvarchar(100) NOT NULL,
LastModified datetime NOT NULL,
Comment nvarchar(300) NULL,
Content nvarchar(max) NOT NULL,
Description nvarchar(200) NULL
I would probably make the PK of the contents table a multi-column key from PageID and Revision provided Revision was an identity type. You would use the Revision column as the FK. You then pull the consolidated record by JOINing like this:
SELECT * FROM Page
JOIN PageContent ON CurrentRevision = Revision AND ID = PageID
There might be some errors up there...this is off the top of my head. It should give you an idea of an alternative pattern, though.
Best Solution
You can always construct a full name from its components, but you can't always deconstruct a full name into its components.
Say you want to write an email starting with "Dear Richie" - you can do that trivially if you have a
given_namefield, but figuring out what someone's given name is from their full name isn't trivial.You can also trivially search or sort by
given_name, orfamily_name, or whatever.(Note I'm using
given_name,family_name, etc. rather thanfirst_name,last_name, because different cultures put their names in different orders.)Solving this problem in the general case is hard - here's an article that gives a flavour of how hard it is: Representing People's Names in Dublin Core.