Re: Relation to a table name

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Henrik Skak Pedersen (notavailable_at_nothing.com)
Date: 12/20/04 

Date: Mon, 20 Dec 2004 21:30:03 +0100

Hi Mark,

Thank you very much for answering. The two tables actually are more than 80
tables each with a different structure. I would like to store documents
against all tables in the 'Documents' table. All my tables have an
autonumber column as there primary key.

Therefore I would create a relation with the table name, the autonumber
field and a description of the attachment as the key, this would always give
an unique key.

My problem is then if I delete a record in one of my tables, do I want to
delete all documents related to the deleted record.

I also want to create an additional boolean field on all my tables
indications whether documents exists for this record. That falg has to be
updates each time I create a document or delete one.

Any ideas?

Best regards

Henrik Skak Pedersen

"Mark" <nospam> wrote in message
news:eAXQ38q5EHA.2316@TK2MSFTNGP15.phx.gbl...
> its possible, in the trigger you'd write
>
> if inserted.TableName = 'Table A' then
> begin
> ' do stuff to table A
> end
> else
> begin
> ' do stuff to table B
> end
>
> Assuming "Table A" and "Table B" will always have the same structure you
> could even have a stored proc that took the tablename as a parameter and
> then does the cascade action, thus reducing duplicated code.
>
> However, IIRC its generally considered bad practice to store data against
> a table name in this way, some people will also not like the fact you save
> data into Documents.TableName that could come from more than one table. If
> table A and Table B are this simple think about merging them into one
> table - solves your trigger problem, much easier to understand and lets
> you remove the "TableName" column from "Documents" table.
>
> "Henrik Skak Pedersen" <notavailable@nothing.com> wrote in message
> news:ugbye2q5EHA.2428@TK2MSFTNGP14.phx.gbl...
>> Hi,
>>
>> I would like to create a relation based on a table name. I have a table
>> schema like this
>>
>> Table A
>> -----------
>> Id
>> Desc
>>
>> Table B
>> ---------
>> Id
>> Desc
>>
>> Documents
>> --------------
>> TableName
>> Id
>>
>> Records in Documents
>> ---------------------------
>> TableName Id
>> ----------- --------------
>> Table A 1
>> Table A 2
>> Table B 1
>>
>> I would like to create a delete action to cascade delete records in my
>> Documents table based on table name and id. How can I do that?
>>
>> Best regards
>>
>> Henrik Skak Pedersen.
>>
>
>