Re: Dependency graph of all tuples relied upon in a query answer

On 8/30/06, Randall Lucas <rlucas(at)tercent(dot)com> wrote:
> I'm storing facts about an entity (e.g., "company") in a normalized
> form with various repeating groups, link tables, etc. My workflow
> requires that after (or as part of) collecting these facts, I be able
> to "sign off" as having verified all of the facts that pertain to a
> given company. I understand this as meaning I need to sign off on each
> row that was used in answering the query "select * from company left
> join ..."

> An inverted way of thinking about the problem is the notion of getting a
> source document (say, a "company registration form") and parsing and
> storing it in a normalized format. How do you "tag" the origin of each
> and every row in every table that resulted from that source document?

your form should have a code, either entered by the customer or by the
preparer who enters it into the database, which becomes the key that
identifies the registration document. Put that key into other tables.

> It is possible to do so by associating an extra column with each
> inserted or modified value (yuck).

be careful, you are flirting with EAV thinking. I think EAV designs
are terrible.

> It seems to me that the elegant way to do this would be to get the
> entire graph of dependencies for not only all tables that reference the
> entity, but only those rows within those tables that refer specifically
> to my entity.
>
> The query engine should have a pretty good idea about which tables and
> which rows would actually be used in forming the responses to a given
> query. How can I get this information? Or am I off the deep end (-ency
> graph) with this one?

I am not sure where you are going with this. Maybe you should mock up
some simple tables and repost your question.

merlin