🔒 Locked

[web] [SQL] Creating a cyclic relation

Started by sanch3x May 6, 2009 at 2:27 PM 3 replies 1.6k views

Original Post

May 6, 2009 at 2:27 PM

Hey, I'm working on a table scheme where a work package consists of multiple tasks. However since the system is bilingual all tasks must be defined separately. I created a recursive relation where the other_language id is a foreign key that references the same table. This will create a cyclic relationship where the english result will reference the french result and vice versa.

CREATE TABLE tbl_Tasks
(
  tid int,
  lid int,
  language varchar(32),
  Primary Key (tid),
  Foreign Key (lid) References tbl_Tasks(tid) On Delete Cascade
)

I'm reading that some sql servers won't allow this so I was wondering if there was a way to get the same behaviour or if there was a way to allow this? I know they wouldn't allow a "on update cascade" because that would create an infinite loop but would it do the same thing (or hang up some other way) with a delete? Thanks for any help [smile]

Nik02

May 6, 2009 at 2:38 PM

I would just create a separate table that contains translations for the task names (metacode follows):

pkey int TranslationId --primary key of this tableint TaskId --to which task this translation appliesvarchar(20) LanguageId --in which language this translation isvarchar(n) TranslationText --the contents of this translation

Then, I would define a query (or a stored proc) that would return a translation given task id and the preferred language; if a translation would not be found for the desired language, a default translation (english, for example) could be returned.

sanch3x

May 6, 2009 at 2:44 PM

So I would have to implement my own cascading routine with this solution? I figured since this was a 1-to-1 relationship that I wouldn't need to make an actual table and instead just merge it in my task table. Thanks for the help.

Nik02

May 7, 2009 at 4:35 AM

It isn't actually 1-to-1 since one Task can have multiple Translations. Since Task and Translation do serve different purposes (even though they would only refer to each other), it is a good idea to keep them in separate tables.

When you need their data merged, it is simple enough to write a join query that pulls data from both tables, anyway.

Cascade delete - as built in to SQL Server 2000 and later - will delete all rows on the secondary table that are "children" of the primary table, so as to enforce referential integrity in such way that each child surviving the delete will still have an actual parent instead of missing reference. This is the very purpose and design intent of the cascade delete. In essence, if this usage fits your scenario, you don't have to handle the deletion of the children separately.

However, if you want to manually do the cascade delete for some exotic reason (or you have a DBMS that doesn't support the abovementioned stuff), you need to delete each child (Translation) recursively from leafs to root before you delete the parent (Task). In this case, the relation is only 1 level deep so you would need to delete the Translations given Task id, and then delete the corresponding Task.

[Edited by - Nik02 on May 7, 2009 4:35:59 AM]

sanch3x

May 7, 2009 at 9:01 AM

We're only working in two languages, I guess the language attribute might have been a bit misleading but that being said leaving it open for additional languages is a good idea.

Thanks again.

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.