Re: Relation to a table name

From: Mark (nospam)
Date: 12/21/04 

Date: Tue, 21 Dec 2004 08:34:29 -0000

Henrik,

I had a feeling it wouldn't be as straightforward as your example, life
never is!

Ok what I'd do is create a "master" table, lets call it "Table C" in here
you'll have a unique ID and any other columns that exists in both Table A
and Table B. Table C is to have a row for every row in Table A and Table B,
this means that where ever the record is stored you have a unqiue ID for it,
this unique ID can then be used in the documents table.
You can now write a simple delete cascade on table C to remove records from
the Documents table.
The only thing more complicated in this setup is if you wish to remove a
document you'll have to delete from table A or Table B first ( which will
have a delete cascade to remove the record from table C ).

"Henrik Skak Pedersen" <notavailable@nothing.com> wrote in message
news:%23iugyIt5EHA.4008@TK2MSFTNGP15.phx.gbl...
> 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.
>>>
>>
>>
>
>