How to handle mututally/recursively related tables?

Question

Hi, I'm pretty new to databases and sql. I have a problem where I have two tables which both contain a foreign key to the primary key of the other. My problem is I have a large number of elements which can have multiple names in different languages, but MUST have a single primary name/language.

Firstly, I want to know if this is possible, or should I just give up already? The obvious solution is to have an extra boolean field in the ElementName table to say IsDefaultName, but it adds some extra complexity for querying and updating. If this is the best solution, how do I constrain the ElementName table to not accept any submission if IsDefaultName is set and the table already has an entry with the same ElementId and IsDefaultName set (or would I need to query this manually)?

I'm attempting to use LINQ to SQL here. The code I'm using to attempt to insert new items throws an exception at SubmitChanges with The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ElementName_Element". I can understand why this is, but wondering if there's a fix/better solution.

var db = new MyDBDataContext();
var element = new Element();
var elementName = new ElementName() {
    ElementName1 = "MyElement",
    Language = "English",
};
element.ElementName = elementName;

db.Elements.InsertOnSubmit(element);
db.ElementNames.InsertOnSubmit(elementName);
db.SubmitChanges();

Answer 1

Why not simply use a self-join like so:

Create Table Elements( 
                        ElementId... Not Null Primary Key
                        , DefaultElementId ... Not Null 
                             References Elements( ElementId )
                        , Name ...
                        , Language ...
                        )

The default name is the one where ElementId = DefaultElementId.

Btw, this is a place where a guid PK is nicer than an identity column. With a guid PK, you could generate both the ElementId and DefaultElementId from the client. If you are using an identity column with this schema, you'll probably have to create a "Unknown" elementId with a known value like zero so that you can do the insert and then turn around and do an update all in a single transaction.

** ADDITION **

Given what you said in comments, it sounds like you are trying to localize the elements data. My inclination would be to recommend adding a non-nullable "Name" column to the Elements table which represents the language neutral or default language name. Your ElementNames table would have a foreign key to the Elements table and would only populate that table when you localized an element name. Your queries would then need to coalesce on the requested language name and the name in the elements table if the requested language did not have a localized name.

Answer 2

Solution 1

element
------------------
element_id
~....

element_name
------------------
element_name_id
fk_element_id
name
language_id
is_default_name  Default ( 0 )

Trigger:
if ( ( select count ( 1 ) from element_name where is_default_name = 1 ) > 1 )
BEGIN
    raisError ( 'only 1 element_name may be marked is_default_name = true.', 16, 1 );
END

Solution 2

element
------------------
element_id ( pk )
~....

element_name
------------------
element_name_id ( pk )
fk_element_id
name
language_id

element_name_default
------------------
fk_element_id
fk_element_name_id
( pk - fk_element_id, fk_element_name_id )

Solution 3

element
------------------
element_id
fk_element_name_id_default NULL
~....

element_name
------------------
element_name_id
fk_element_id
name
language_id

order of code:
* Insert to element_name
* update of element

I would stick with what you had, cause it is just fine, just:

db.Elements.InsertOnSubmit(element);
db.ElementNames.InsertOnSubmit(elementName);
//I don't know this syntax to say
//  set the property of element.fk_element_name_id_default 
//  to the newly inserted elementName from above
db.Elements.?.?