Re: Denormalization problem
From: Karel Miklav <karel_at_inetis.spppambait.com>
Date: Fri, 30 May 2003 07:23:16 +0200
Message-ID: <6VBBa.482$78.10685_at_news.siol.net>
>>I have a multilingual database with this design pattern:
>>
>>car ( PK(id), wheels, engine_id )
>>engine ( PK(id) , last_property)
>>engine_description ( PK(engine_id, language_id), description )
>>
>>Objects have parts and parts have descriptions in different languages.
>>Parts are generated in run time and descriptions in all languages are
>>never known. When 'engine' loses it's 'last_property' I'm tempted to
>>delete it, but thus I also lose referential integrity. What am I
>>supposed to do; denormalize and throw away the integrity, tolerate that
>> id-only tables or something completely different?
Date: Fri, 30 May 2003 07:23:16 +0200
Message-ID: <6VBBa.482$78.10685_at_news.siol.net>
Nelson Ricardo wrote:
> Karel Miklav wrote:
>>I have a multilingual database with this design pattern:
>>
>>car ( PK(id), wheels, engine_id )
>>engine ( PK(id) , last_property)
>>engine_description ( PK(engine_id, language_id), description )
>>
>>Objects have parts and parts have descriptions in different languages.
>>Parts are generated in run time and descriptions in all languages are
>>never known. When 'engine' loses it's 'last_property' I'm tempted to
>>delete it, but thus I also lose referential integrity. What am I
>>supposed to do; denormalize and throw away the integrity, tolerate that
>> id-only tables or something completely different?
> > Rather than deleting the engine, add a column to that table. It > should be char(1) and named "inactive". Set the value to "Y" for > those that are no longer active (i.e., in essence marking them as > "deleted" without realling deleting); set the value to "N" for those > that are active. This is how our accounting software handles deleted > entries and inactive codes.
Sorry Nelson, I wasn't clear; I wonder whether the 'engine' table should be removed or not.
Thanks,
Karel Miklav
Received on Fri May 30 2003 - 07:23:16 CEST