3

I have a scenario in a very old and very large application, where I have a table representing a type of resource:

CREATE TABLE resource (resource_id INT, name NVARCHAR(4000))

This table is selected from in hundreds of different places, including stored procedures and dynamic SQL in the application code.

A team recently updated this resource's name to be localized, and their approach is pretty straight forward. There is a new table containing the localized names, and a 'default' language ID on the resource table, for when the name isn't localized for the requested language:

-- Foreign keys omitted
ALTER TABLE resource ADD default_language_id INT
CREATE TABLE resource_local (resource_id INT, language_id INT, name NVARCHAR(4000))

Most procs have an @user_language_id parameter, so the logic for choosing the name to return is simple: take resource_local.name matching language_id = @user_language_id if it exists, otherwise resource_local.name matching language_id = resource.default_language_id if it exists, otherwise take resource.name.

Unfortunately, this turns the logic to select the correct name into something like this:

SELECT ISNULL(ISNULL(exact.name, default.name), res.name)
FROM resource res
LEFT JOIN resource_local exact ON exact.resource_id = res.resource_id 
    AND exact.language_id = @user_language_id
LEFT JOIN resource_local default ON default.resource_id = res.resource_id
    AND default.language_id = res.default_language_id
WHERE res.resource_id = @resource_id

All of the hundreds of places that try to select resource.name are having to be updated with this logic, which has turned this project into a massive effort across the entire organization, as each team needs to update their SQL to use this logic. This also causes maintainability issues, as any new developers dealing with this table need to know that they can't just use the name column.

It's too late now, but I'm curious: is there any better way to approach this, so that selecting the name column from resource will just 'do the right thing' based on the @user_language_id variable (if it exists)?

4

I'm not sure if it's possible to do this so that none of the references to the resource table need to change. It seems like the fact that a language_id is needed is a fundamental change that all calling code will need to be aware of.

However, it is possible to design this in a way that the resource can be queried in either of the following simple ways. One of these options might have been an easier change to make and maintain across so many different places.


Table-valued function

Using an Inline Table-Valued Function, we can provide the following syntax.

SELECT resource_id, language_id, name
FROM dbo.resourceTVF(@resource_id, @language_id) r

Here is an example of how to create the function. It's essentially the same query from your question, but with the alias default changed to def (default is a SQL Server keyword).

-- Create the Table-Valued Function
CREATE FUNCTION dbo.resourceTVF (@resource_id INT, @user_language_id INT)
RETURNS TABLE 
AS
RETURN
SELECT @resource_id AS resource_id,
  @user_language_id AS language_id,
  ISNULL(ISNULL(exact.name, def.name), res.name) AS name
FROM dbo.resource res
LEFT JOIN dbo.resource_local exact ON exact.resource_id = res.resource_id 
    AND exact.language_id = @user_language_id
LEFT JOIN dbo.resource_local def ON def.resource_id = res.resource_id
    AND def.language_id = res.default_language_id
WHERE res.resource_id = @resource_id
GO


View

You could rename the resource table (e.g., to resource_base) and then create a resource view in order to provide the following API:

SELECT resource_id, language_id, name
FROM dbo.resource
WHERE resource_id = @resource_id
  AND language_id = @language_id

The primary downside is that the view definition needs to CROSS JOIN all resources and languages before using applying the LEFT JOIN to the local and default resources. Even so, this is going to be a fairly efficient plan with 4 singleton seeks assuming that you have the proper indexes.

CREATE VIEW dbo.resource WITH SCHEMABINDING AS
SELECT res.resource_id,
  lang.language_id,
  ISNULL(ISNULL(exact.name, def.name), res.name) AS name
FROM dbo.resource_base res
CROSS JOIN dbo.languages lang
LEFT JOIN dbo.resource_local exact ON exact.resource_id = res.resource_id 
    AND exact.language_id = lang.language_id
LEFT JOIN dbo.resource_local def ON def.resource_id = res.resource_id
    AND def.language_id = res.default_language_id
GO


Full script

Here is a full script where I implemented both of these proposals, loaded a small amount of fake data, and ran a few test cases. At least for these test cases, both approaches yield the desired results and use a loop-seek based plan.

I think that the inline table-valued function is probably the approach the I'd try first. Note that you can use CROSS APPLY to "join" to the table-valued function if you need more than one resource at a time.

  • I've already said that I like the iTVF approach, but I would also like the View if it was an Indexed View. I can't imagine these labels changing that often, which makes this a good candidate for indexing it. Renaming, while nice, should wait until 100% of the code has been updated since none of it was passing in language_id hence it would match multiple rows of this view. Lastly, I wouldn't consider the languages as the master set. I would use the resource table and LEFT JOIN to resources_local on resource_id. – Solomon Rutzky Oct 14 '15 at 14:19
1

I definitely 2nd @Geoff's Inline Table-Valued Function (iTVF) approach and was going to suggest that myself, but he beat me to it ;-).

I will just add that it seems like having 2 levels of "defaults" seems a little convoluted. I mean, I don't get why the default_language_id was added to the resource table. It would seem to allow for various resources on a given page to be sourced from a variety of languages. I think it is more consistent for the end-user if you simply had the locale / LCID -based resource in the new table, and if not found, then find the default in the resource table. But going back there first only to get a default language_id? I think in the long-run that will cause more problems than it will solve. If you are going to have a default, there should be one default. Either you find the resource name in the desired LCID, or you don't and fall back to the default (although, you probably shouldn't let that happen, since a page that has both English (left to right) and Hebrew or Arabic (right to left languages) might be a little confusing ;).

  • 1
    Yea, the multiple defaults was a little confusing to me too. Maybe it was for legacy reasons there will be some process filling in the resource_id/name and then another process that will update with the translations before that resource is officially ready to be used in the application? That sounds a little convoluted though, so I wasn't able to think of a great reason for the multiple defaults. – Geoff Patterson Oct 14 '15 at 13:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.