In my app I have many localizable entities. Database structure for this entities
is . Types of PK in some tables may be differ (some of them is int, some is bigint). It depends on how much data will be stored in a table.
I use Dapper as ORM.
Now I have this solution (but something inside tells me that this solution is bad):
// ENTITY
public abstract class Entity
{
public object Id { get; set; }
}
public abstract class Entity<TKey> : Entity
{
public new TKey Id { get; set; }
}
// LOCALIZABLE ENTITY
public abstract class LocalizableEntity<TTranslation> : Entity
where TTranslation : EntityTranslation
{
public ICollection<TTranslation> Translations { get; set; }
}
public abstract class LocalizableEntity<TKey, TTranslation> : Entity<TKey>
where TTranslation : EntityTranslation
{
public ICollection<TTranslation> Translations { get; set; }
}
// ENTITY TRANSLATION
public abstract class EntityTranslation
{
public object LocalizableId { get; set; }
public int LanguageId { get; set; }
}
public abstract class EntityTranslation<TKey> : EntityTranslation
{
public new TKey LocalizableId { get; set; }
}
// REPOSITORIES
public class BaseRepository: IRepository, IDisposable
{
public string ConnectionString { get; set; }
// ....
}
public abstract class BaseEntityRepository: BaseRepository
{
protected IDbConnection Connection => _connection ?? (_connection = CreateDbConnection(GetConnectionStringValue(ConnectionString)));
protected abstract IDbConnection CreateDbConnection(string connectionString);
// SaveEntity<T>(T entity), DeleteEntity(object id)
}
public abstract class BaseEntityRepository<TEntity, TKey, TSearchOptions, TLoadOptions> : BaseEntityRepository
where TEntity : Entity<TKey>
where TSearchOptions : SearchOptions
where TLoadOptions : LoadOptions
{
// GetEntities(TSearchOptions sopts, TLoadOptions lopts), EntityCount(TSearchOptions) ...
}
public abstract class BaseLocalizableEntityRepository<TEntity, TKey, TEntityTranslation, TSearchOptions, TLoadOptions> : BaseEntityRepository<TEntity, TSearchOptions, TLoadOptions>
where TEntity : Entity<TKey>
where TEntityTranslation : EntityTranslation<TKey>
where TSearchOptions : SearchOptions
where TLoadOptions : LoadOptions
{
// GetTranslations, SaveTranslation ...
}
Is it good or bad? If it is bad, how should I do it?
As you need highly customizable localization your solution is not so bad. I perform the same way if I need translation. But instead having a different table per entity-translation I have a unique table to which any entities that support translation reference to:
CREATE TABLE Test_Translations
(
Language char(10) NOT NULL,
TextId int NOT NULL,
Value text NOT NULL,
CONSTRAINT Test_Translations_Language_TextId_pk PRIMARY KEY (Language, TextId)
);
CREATE TABLE Test_LocalizableStrings
(
Id int NOT NULL CONSTRAINT Test2_Test1Id_pk primary key
);
ALTER TABLE Test_Translations ADD FOREIGN KEY (TextId) REFERENCES Test_LocalizableStrings;
Then in the tables that need a translation support just create a reference key to Test_LocalizableStrings. At query time query for TextId
and Language
.