adding an auxilary table to an "untouchable" base table

I've got a table set up by some consultants and heavily utilized by their
proprietary (read "unreadable, untouchable, unstable") code base.
Never the less, I do have full access to the database and have in mind
extending some of their tables, but I don't know how to best solve the
problem.
Basically, for a base table I want to create a parallel extension table
joined by a reliable primary key (e.g. identity column). Ideally, I want to
create a view that unifies the two (base and auxilary) so that I can treat
the composite view EXACTLY like a single table (i.e. for all forms of select
/ update / insert / delete). Ideally, I would have the auxiliary table be
sparsely filled (nulls are allowable for all the auxilary fields).
TIA!Hi
If this is unstable then maybe you need to get that sorted before adding to
the system and becoming more reliant on it?
You may want to consider replicating the database in some way so that it is
not effecting the live system and then make changes. If you add triggers to
insert/update information in your new tables then they may mean that you tak
e
over the responsibility for this.
John
"slintz" wrote:

> I've got a table set up by some consultants and heavily utilized by their
> proprietary (read "unreadable, untouchable, unstable") code base.
> Never the less, I do have full access to the database and have in mind
> extending some of their tables, but I don't know how to best solve the
> problem.
> Basically, for a base table I want to create a parallel extension table
> joined by a reliable primary key (e.g. identity column). Ideally, I want
to
> create a view that unifies the two (base and auxilary) so that I can treat
> the composite view EXACTLY like a single table (i.e. for all forms of sele
ct
> / update / insert / delete). Ideally, I would have the auxiliary table be
> sparsely filled (nulls are allowable for all the auxilary fields).
> TIA!|||Thanks for the ideas, but they are off my needs target. I wrote the first
post in a rush, so here's a "neater" version of the problem:
I have a table (Inventory) which is part of a third party system. The
outside developers keep all source code proprietary ("unreadable") and while
the database is fully exposed, they make schema modifications from time to
time ("unstable" - perhaps a better word is "volatile"). Thus, to keep my
extentions as separate as possible from their on-going development changes,
I'm looking for a solution that will effectively add columns to their
("base") table using a one-to-one relationship to an auxilary table.
I would like the composite to be usable through a view, so it behaves like a
table and such that I don't have to manage the auxilary records directly
(they would be "automatically" created when needed, e.g. when an auxilary
field is updated, or when a new base record is created). Also, the base
table will still be accessed directly (for all query types) from the
third-party code base so the solution needs to allow for "missing" auxilary
records.
What I've tried (and doesn't fully work :( is to create the auxilary with a
foreign key related to the primary of the base plus a simple view made from
a
LEFT OUTER JOIN (base table on left, auxilary on right). Selection works
fine, but when I try to update an extended field, it SILENTLY fails <grrr>.
When I try to insert a new row providing values for both the base and
auxilary tables I get an error message ("View or function 'TestPlus' is not
updatable because the modification affects multiple base tables.").
This looks bad... as if there is no way to do what I want. Perhaps some
clever person out there (you?) know a trick or even an alternate approach
that will work (still providing as much of a "pure" table seeming solution a
s
possible).
Hope this clearificationism really is one... <smile>.
PS: Also, I can't use triggers on the base table - they "belong" to the
third-party system...
"John Bell" wrote:
> Hi
> If this is unstable then maybe you need to get that sorted before adding t
o
> the system and becoming more reliant on it?
> You may want to consider replicating the database in some way so that it i
s
> not effecting the live system and then make changes. If you add triggers t
o
> insert/update information in your new tables then they may mean that you t
ake
> over the responsibility for this.
> John
> "slintz" wrote:
>|||Hi
I am not sure why the primary keys of the main table are not duplicateed as
primary keys of the auxiliary table as you are only expecting a 1-1 mapping.
Using your view you can access information from both the main table and
auxiliary table and using instead of triggers on this view you can control
when the auxilliary table is inserted/updated. You should not need an
inserted trigger. Your update trigger can update existing records and add ne
w
ones using something like:
/* Update existing records */
UPDATE A
SET col1 = i.col1,...
FROM Auxillary A
JOIN INSERTED i ON i.pk = a.pk
/* Add missing records */
INSERT INTO AuxilliaryTable ( pk, col1, ... )
SELECT i.pk, i.col1, ... FROM INSERTED i WHERE NOT EXISTS ( SELECT * FROM
Base b where i.ok = b.pk )
What you can't guarantee is that the PK column is going to stay the same,
but if you exclude yourself from add triggers to the base tables you should
not be allowed to add cascading FKs.
John
"slintz" wrote:
> Thanks for the ideas, but they are off my needs target. I wrote the first
> post in a rush, so here's a "neater" version of the problem:
> I have a table (Inventory) which is part of a third party system. The
> outside developers keep all source code proprietary ("unreadable") and whi
le
> the database is fully exposed, they make schema modifications from time to
> time ("unstable" - perhaps a better word is "volatile"). Thus, to keep my
> extentions as separate as possible from their on-going development changes
,
> I'm looking for a solution that will effectively add columns to their
> ("base") table using a one-to-one relationship to an auxilary table.
> I would like the composite to be usable through a view, so it behaves like
a
> table and such that I don't have to manage the auxilary records directly
> (they would be "automatically" created when needed, e.g. when an auxilary
> field is updated, or when a new base record is created). Also, the base
> table will still be accessed directly (for all query types) from the
> third-party code base so the solution needs to allow for "missing" auxilar
y
> records.
> What I've tried (and doesn't fully work :( is to create the auxilary with
a
> foreign key related to the primary of the base plus a simple view made fro
m a
> LEFT OUTER JOIN (base table on left, auxilary on right). Selection works
> fine, but when I try to update an extended field, it SILENTLY fails <grrr>
.
> When I try to insert a new row providing values for both the base and
> auxilary tables I get an error message ("View or function 'TestPlus' is no
t
> updatable because the modification affects multiple base tables.").
> This looks bad... as if there is no way to do what I want. Perhaps some
> clever person out there (you?) know a trick or even an alternate approach
> that will work (still providing as much of a "pure" table seeming solution
as
> possible).
> Hope this clearificationism really is one... <smile>.
>
> PS: Also, I can't use triggers on the base table - they "belong" to the
> third-party system...
>
> "John Bell" wrote:
>|||On Wed, 21 Sep 2005 10:36:01 -0700, slintz wrote:

>Thanks for the ideas, but they are off my needs target. I wrote the first
>post in a rush, so here's a "neater" version of the problem:
(snip)
Hi slintz,
My first choice would be the following:
* Make a new table that holds all columns in the third-party table plus
the extra columns you need;
* Make a view that holds only the columns that were in the third-party
table, give this view the name of the orifinal table.
This way, the third-party software wil continue to work, thinking it's
using the original table. But you'll still have all data in one table.
If all your extra columns are optional, you don't even need to code any
triggers to pull this off.
But if such a change would violate your license, then use this second
choice setup:
* Make a new table that holds the extra columns. This table should have
the same primary key as the third-part table. Set up a foreign key
constraint from the PK in the extra table to the PK in the thrid-party
table. This effectively creates a 1 to (0 or 1) relationship between the
third-party table and the extra table.
* Make a view that joins both tables and returns the columns from both.
* If you want to modify through the view, set up some INSTEAD OF
triggers on the view to translate your modifications to the correct
operations on the third-party table and the extra table.
* If all columns in the extra table are optional, then there's no need
to do anything if a row in the thrid-part table gets inserted. The
INSTEAD OF UPDATE trigger on the view will figure out if there is
already a row in the extra table, and insert or update as appropriate.
* Depending on what you want to do if the third-party application
deletes a row in it's table, setting the ON DELETE CASCADE option for
the foreign key constraint might suffice to handle that part.
* If the PK can be changed, then use the ON UPDATE CASCADE option as
well.
Regardless of which of these versiojns you choose, you will have to
change and re-test whenever the vendor changes the table structure.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)