0

I am having this Entity (both in database and Entity Framework):

public class LanguageValue
{
    public int ID { get; set; }
    public long Key { get; set; } // bigint in database
    public int LanguageID { get; set; } // FK with a Language table in database
    public string Value { get; set; } // nvarchar(MAX) in database
    public bool Active { get; set; } // bit in database
}

I have already read this article, but my problem is a bit different: EF6: How to generate a unique number automatically based on entity Id

My Key column does not have unique constraint, because there can be multiple rows with the same Key value. For example:

ID   Key   LanguageID    Value
---------------------------------------------------
1    1     1             Name in Language 1
2    1     2             Name in Language 2

I cannot use a Mapping table to enforce a FK instead of Key because multiple tables uses this same table (Product's Name, Category's Name, Article's Content).

Now whenever I need to insert a Product or Category record, I need to insert their corresponding Language Value. However, I have yet to find a way to generate a Key that is unique among them, and more over, need to be unique in case two insert are being processed at once (for example, adding a Product and Category at the same time).

I am thinking about Guid, should that be my safe bet with RNG, or is there any better way/design for achieving it?

Community
  • 1
  • 1
Luke Vo
  • 17,859
  • 21
  • 105
  • 181
  • ID is your unique identifier.. how is that different from what you're wanting to do? – JamieD77 Oct 26 '15 at 18:34
  • For every Insert operator, I need to insert 2 rows (or more depends on how many language), with the same `Key`, but except those 2 rows, no other row may have that `Key` value. So it is not the ID. – Luke Vo Oct 26 '15 at 18:36
  • And I would like to insert all `Product` and its 2 `LanguageValue` records in one transaction, so in EntityFramework, I do not have the ID yet. – Luke Vo Oct 26 '15 at 18:37
  • Well guid would work if you're not willing to create new entities like `ProductLanguages, CategoryLanguages`, etc.. – JamieD77 Oct 26 '15 at 18:41
  • another option would be to add nullable foreign key fields to your LanguageValue entity for each of the different entity types – JamieD77 Oct 26 '15 at 18:46
  • @JamieD77 I have considered that option, but it would waste too much space when the number of entities increase. However, I am now trying another solution I just think about. I am creating a `LanguageKey` table instead (which act as the current key), and a real `LanguageValue` table and have FK with the `LanguageKey` table. – Luke Vo Oct 26 '15 at 18:49

1 Answers1

2

OK, I think my problem was from bad database design. I seperated the LanguageValue table into two tables, like the following diagram, and realize 2 pros that the previous design did not have:

enter image description here

  1. I can now safely insert the LanguageKey with IDENTITY.
  2. I can now have FK for Product and Category's NameLanguageKey, which were impossible before because Key was not unique.
Luke Vo
  • 17,859
  • 21
  • 105
  • 181
  • glad you found a good result. btw - imho, by only presenting the LanguageValue table/class in your OP, i think you obscured your problem. the above extended diagram explains far better what you were after.. anyway, plus 1 for sharing the final solution – jim tollan Oct 26 '15 at 21:22