In my database I have a table that is used to store document references - namely the source of the data being captured in all other tables. I call this table "InfoSource". Thus each table in the database may have multiple foreign key references to an entry in "InfoSource".

For instance, in a table called "person" I may have the following fields: "birthday", "weight", "height", "favorite color". For each field I would have an additional field "birthday_infosource", "weight_infosource" etc. that is a foreign key to an entry in "InfoSource".

Now I would like to create a cross reference. Given a record in "InfoSource" which records across all other tables point to it?

Right now the way I do this is via a query with a bunch of unions, where I query each individual table. The drawback is that every time a new table is added or a table is changed, I have to update the cross-reference query.

Does it make sense to implement this via a stored procedure? In pseudo code I can think of something like this:
Unfortunately, I have no idea how I'd begin to even implement this in a SP. I'm open to suggestions.

Thanks!
Carlos

Granted that I'm an odd duck and my databases all have a common surrogate key structure (the keys aren't all exactly alike, but they are all single-column and one datatype). This would allow me to build one table to track usages (essentially identifying a column within a table), and then a simple table that tracked that usage along with a PK (Primary Key) value (from the row) and to associate that with a given InfoSource row (PK). Then it becomes a simple matter to index the association table and query to my heart's content!

-PatP

personally I call that an EAV type design, and it nearly always results in a nightmare. So much of so I avoid it like the plague

OK - you caught my attention. Why would you say this is an EAV type design?

Hi

This EAV Data Model omn my Database Answers Web Site might help you :-
User-Defined Hierarchies Data Model

HTH

Barry Williams
London, England

Yes, it's an EAV design.
Yes, EAV should be avoided.

Consider using an XML datatype to store dissimilar fields, if your platform supports it.