Hi Brian,
Some responses inline...
Brian Shields wrote:
Rick Hillegas wrote:
Hi Brian,
Combining data from two databases can be done by using database
procedures or table functions, but you will probably get better
performance if you can just maintain two separate schemas in the same
database.
What you are trying to accomplish sounds a lot like an outer join. You
might want to read about this kind of join here:
http://en.wikipedia.org/wiki/Join_%28SQL%29
Thanks for the help, that looks exactly like what I need. However, I've got two
questions:
1) Using two schemas in one database, how does Derby store the filesystem?
Currently, I've got a Data directory, containing a couple files, a log folder,
and a seg0 folder with many data files. If the two schemas are stored separately
(or in separate subfolders), adding database updates to my program should be
trivial. If the data from the two schemas is merged into the same data files,
updating the non-user data would be more tricky.
You'll see one file per table in the seg0 folder. The schemas will not
share data files.
2) I don't think it would come up, but it doesn't seem that there's any
possibility with this method to detect if a row has been removed by the user. If
Data contains row 'foo', and the user deletes 'foo', this method seems that it
would return 'foo' from Data, and a set of NULLs in the corresponding User_Data,
which is the same result as if the user had done nothing.
Of course, as I write this, I think I may have come up with a solution to that
problem, as well; adding an extra column to the User_Data (boolean, likely) to
indicate if the row should be deleted. Does that seem reasonable?
Sounds reasonable to me. You'll have to track history yourself; the
outer joins won't do that job for you.
Regards,
-Rick
