Searching across linked databases

[quant]

Originally posted by ashwken
Let's try a different case for Multiple db Search.

I've got a db for Lead Tracking which contains default Contact records and history of interaction.

I've got another db for Transactions Listing / Sales Tracking which contains custom Contact and other forms.

Although both databases have the common element of being related to my work (and being somewhat Contact centric), are they disimilar enough in purpose to warrant separation?

I'm not sure I was convinced. What is the decisive "thing" in this case that made you split it into two databases as opposed to just being two different directories in the same db?

Originally posted by ashwken
[B]You can create a link (copy w/url, paste to rtf) between the corresponding Contact records upon the first instance of a Lead taking part in a Transaction - each record would require a link to the other.

I probably don't understand all details, but from the database design point of view, all these seems to me like a perfect example for just having different tables in the same database. Plus in 3.5 beta, the transaction table (transaction template) could have a contact as attribute (Info Item attribute), nice.

[tfjern]

After reading ashwken's post, it occurred to me once again that there are basically two groups of people using Ultra Recall, and the rest spread out in between: a) power users like ashwken, who frown on those who commit database blunders such as duplicating data, and b) haphazard, bumbling users like me, who use Ultra Recall merely to store assorted data as it is collected ad hoc (web pages, emails, pdf files, xps files, docs, you name it, I store it, and rarely have time or energy to label entries with attributes), ala Evernote, AskSam, OneNote, or the multitude of other such user-friendly programs available.

What a waste, you may say. I realize I'm not getting as much as I could from using Ultra Recall, but still it's better than the above-mentioned programs, that is, it works quite well for the purposes I need, minus a global search engine, but this deficiency can be handled by third-party software.

[StephenUK]

It seems to me that the request to search across several databases is well made.

I agree that different databases should ideally contain different types of information. But sometimes one later on forgets where one put something, or indeed, simply mis-files.

The ability to search all databases would not mean having to do so all the time! The default would presumably be only to search the current database (or branch of a database), but with the further ability to make a search of all (or perhaps specified) databases by eg ticking a box.

Yes, those searches of all databases would generate more noise, but they would only be made where necessary and the noise would be a small price to pay when wondering in which database one had placed something.

InfoSelect allows searching of several databases when needed and it is a very useful facility. Certain databases or parts of databases can be excluded from a search to keep down noise.

[ashwken]

Originally posted by tfjern
After reading ashwken's post, it occurred to me once again that there are basically two groups of people using Ultra Recall, and the rest spread out in between: a) power users like ashwken, who frown on those who commit database blunders such as duplicating data, and b) haphazard, bumbling users like me, ...

tfjern,

My apologies for coming across in this manner, it was never my intention to insult or offend you. My intention has been to highlight the struggle that many of us face when using this program.

This is an imperfect medium for communication.

[ashwken]

Originally posted by quant
I'm not sure I was convinced. What is the decisive "thing" in this case that made you split it into two databases as opposed to just being two different directories in the same db?

Part of it is that the databases have been created and developed at different times, evolving under my learning curve (of both UR and my workflow) and the evolving capabilities of UR.

Another part of it stems from the (growing) size of the Lead Tracking db, and later the Transactions db (may be a workflow problem in both cases). Size relates to both operational performance (possibly subjective) and backup (originally restricted to CD media, change to other media is now possible).

The Lead Tracking db uses the default UR Contact record to accept a Contact from Outlook. An Outlook record is built for each Lead that comes in. The record in Outlook needs to exist so that I can email from the Multiple Listing Service (MLS) program. This Outlook record is pretty much the master.

The Lead Tracking db has various folders that represent different stages of interaction with the Contact. For example, when initally sending a Contact record from Outlook to UR, there are a number of target folders that correspond to the source type of the Lead (a number of specific pubs, our website, other), the Default Child Item for each of these folders contains distinct default data.

This Default Child Item is a Folder that is entitled with the name of the Contact, the contact record is moved to a child of this Contact Name folder. Its Default Child Item is a Contact Event Folder which will contain the various things that constitute the Event (emails and such, or the Detail Pane could be text notes of the event with no children).

\Lead Source Type\new Contact Item
\Lead Source Type\Contact Name Folder


\Lead Source Type\Contact Name\Contact Item
\Lead Source Type\Contact Name\(date) Event
\Lead Source Type\Contact Name\(date) Event\email

The continued growth of this db is directly related to my workflow of moving the relavent emails from Outlook into UR. In many instances these emails contain sizable attachments (images, pdfs, ...). This may be a case of improper workflow (design decesion) - perhaps it would be better to manage these emails within Outlook (moving them to a separate pst), then link/sync them from UR (which is the more reliable format, pst or urd).

Another area that may need to be re-examined is the use of the Contact Name folder. This was originally utilized to track user defined Attributes in UR, it now may be the case where these Attributes can be created in Outlook and "carried thru" to UR, eliminating the need for the Contact Name Folder. Or maybe I'm not fully utilizing Outlook Categories and how they translate to UR keywords.

After the initial response (Contact Event) to the new Lead, the Contact Name folder is moved to another Folder that correspondes to a level of activity - Lead Processed (waiting for some response), Link Accessed (a weblink within an email has been accessed), Working (on-going interaction), Inactive (Removed-Bad Info).

\Lead Source Type\

\Lead - Processed\Contact Name\
\Lead - Link Accessed\Contact Name\
\Lead - Working\Contact Name\
\Lead - Inactive\Contact Name\

This part of the workflow seems to be a toss-up between physically managing the differnt aspects of Lead interaction (represented by the folder structure), or "flattening-out" the tree and managing the Lead interaction with a UR Attribute and pre-defined Searches (hoisitng, favorites).

==========

The Transaction (Listing / Sales) Tracking db takes a slightly different tact in dealing with Contacts. Here I'm having to use a Custom Contact Form due to needing more Contact fields than are recognized by UR from Outlook. And I'm having to create individual Contact records for each person. This is a modeling requirement - a person can enter into a Transaction as an individual or as part of a group (depends on how the Deed is made out). I've also got a custom Contact Form for Vendors.

For a Listing there are various supporting docs (emails, pdfs, word docs,...) that are linked or stored in the db. A Listing also requires additional tables (tree branch) that deal with Adv Tracking (both specific to a Lisitng and generic for the Firm), Lockbox Tracking, and Signage.

For a Sale there are the various supporting docs and such.

This db is also growing in size due to the storage of emails (and their attachments), again could be a workflow problem.

Listing and Sale transactions remain in the db even after completion.

Originally posted by quant
I probably don't understand all details, but from the database design point of view, all these seems to me like a perfect example for just having different tables in the same database.

This is probably more detail than you wanted, but talking this thing thru may be of benefit. I can see where you are coming from, unfortunately the choices we make are based on our understandings at the time. I'm not sure if I've made a convincing case or simply revealed areas in my work that need addressing.

[HalfCyborg]

I'd just like to add my personal bump (+1) to this feature request.

I ALWAYS have UR open when I'm browsing the web. If I see anything of interest that I might like to refer to later (or maybe just read later), I click on the UR icon in the browser to import the page into UR.

I start a new database about every three months, because on a new, empty database, the imports are virtually instantaneous. Once the database gets up around 300-500 MB, the imports get really, really slow.

I've got some fantastic reference articles stored up over the last couple of years (mostly programming topics), and I would LOVE to be able to search them all at the same time. Instead, I find myself saying, "Now what month was I reading about that, I know I saved it in one of these databases..."

Another reason to create multiple databases for "bulk data" like this is to facilitate differential backups. Only my "current" database needs regular backups, the "legacy" databases from previous months have long since been burned to DVDs. If I kept everything in one DB, it would currently be over 12GB in size (which I'd have to back up every day - bleech). If I could only search them without opening them one-by-one...

[quant]

Originally posted by HalfCyborg
I'd just like to add my personal bump (+1) to this feature request.

I ALWAYS have UR open when I'm browsing the web. If I see anything of interest that I might like to refer to later (or maybe just read later), I click on the UR icon in the browser to import the page into UR.

I start a new database about every three months, because on a new, empty database, the imports are virtually instantaneous. Once the database gets up around 300-500 MB, the imports get really, really slow.

I've got some fantastic reference articles stored up over the last couple of years (mostly programming topics), and I would LOVE to be able to search them all at the same time. Instead, I find myself saying, "Now what month was I reading about that, I know I saved it in one of these databases..."

Another reason to create multiple databases for "bulk data" like this is to facilitate differential backups. Only my "current" database needs regular backups, the "legacy" databases from previous months have long since been burned to DVDs. If I kept everything in one DB, it would currently be over 12GB in size (which I'd have to back up every day - bleech). If I could only search them without opening them one-by-one...

I'm still not convinced
You can keep everything but the latest one in a single DB (which you wouldn't need to back up every day) ... and you'd need to search only two DBs, the current one, and your "legacy" one, and only in the case that you are sure that it's not in the current one ... ;-)

What we need, IMHO, is to be able to run external searches on the UR databases. Did you ever try to find sth on your 12GB repository? Say "operator overloading" ... you'd need to play a long time to really find what you want, because you could either search for exact phrase, or for single keywords. But these keywords could be very far from each other, and you'd get items that are not what you really want. Also, UR's search won't recognize how many times are the keywords found, so when you get 100 hits, you don't know which one is the most probable you are looking for ... and many many other features that proper search programs offer.

That's the reason why non-essential stuff is only linked to UR in my case. I'm 100000% sure you'd do better if all your websites that you accumulated are just in the standard Windows directories that are indexed with one of the powerful search index engines that UR cannot compete with.

[mikeg]

Sorry I don't have time at the moment to read this thread as carefully as it warrants or to make an equally considered response, but I wanted to jump in with my own desire for searching across multiple databases.

In my case, since I deal with computing topics both inside and outside my job I've settled on having one database for work and another for home. There is inevitable overlap. However, with no easy way that I'm aware of to keep the two synchronized, and with some home material being inappropriate for work anyway, I now maintain a home and work URD which have an estimated 30% overlap in types of information, but less than 1% exact match on specific information items.

Anyway, for me it is simpler to back up the latest home URD to my work machine and vice versa, then open and search across both as needed.

As a DBA and data architect, I'm familiar with the need to avoid redundant data and redundant databases, but, as tfjern and others have expressed in their own words, a database designed to store ad hoc information and free users from the rigid confines of modeling and normalization would benefit from cross-domain search capability. Cross domain/database search increases opportunity for browsing information that is hopefully not redundant, but is tightly or loosly related.

Now this can get kind of scary with gigantic databases and expanding into the realm of desktop search engines if the user's goal is to store most or all content (ALL documents, etc.) within UR and expect a search in UR to find everything on the computer. I avoid huge databases (and search results) by using UR for storing important ad hoc infomation scraps (from notes, Web pages, docs, emails, etc.), but only linking to larger information stores such as folders containing related, but numerous and bulky items. This system depends in part on a habit of maintianing reasonably organized folder structures outside of UR as well as inside.

Search across multiple open UR databases would just be another tool to help fill the gaps and make life easier while allowing users to work more the way they want... Hope I didn't drift off point here by skimming through too quickly.

[wordmuse]

Jumping in where both angels and demons fear to tiptoe...

I don't see even the slightest logic problem related to being able to search across databases. That is, if the search function enables you with something like this:

a - search current database only
b - search all open databases
c - search all URDs in this folder
d - search all URD in this and all subfolders
e - search all URDs wherever they may be accessed
f - search the following databases (and use your mouse to pick multiple databases to search)

Seems to me that if I had all of these options (are there any I'm missing?), then I could do what is available to me now, and much more.

At least that's the way I see it. Am I missing something?

[mikeg]

I haven't put nearly as much thought into this as others may have, but this looks like a great cross-URD search functionality list to me.

p.s. Small edit on my previous post: "... is tightly or loosly related" should read "... could be tightly or loosely related". Also, cross-database search would be the best way to spot redundant information so it can be pruned or not according to preference. Number of databases should also be according to preference within the limits of the architecture.

Relaxed rules are appropriate this kind of unstructured data. Now if the discussion is about nodes of structured data that should be normalized, such as address info, that's another story...