I have been developing an accounting system for over 7 years for about 30 clients. A common request is: we want to add a field to table AA to hold group codes so that we can subtotal report on those groups. Then it turns out that they want to be able to code some AA records in more than one group. I'm sure this is a common request no matter what the app is. What I would like to do is this: a table of valid groups (VG), and a table of intersections between table AA and the valid groups (AG).

create table AA ( cAaID c(6) primary key, ...useful data about AA records.. )
create table VG ( cVgID c(6) primary key, cVgDsc c(40) )
create table AG ( cAgID c(6) primary key, cAgVgID c(6) references VG, cAgAaID c(6) references AA  )

a picture of the above:

http://www.personnelware.com/samples/vintsec1.gif

So far this is a text-book case. The problem is the next request:: "We want to be able to code table BB, CC... using the same list of codes." I could just set up more intersection tables (BG, CG...), but then the user interface (or something) needs to be modified to work with different intersection tables AG, BG, CG...

It seems that it would be better to set up the intersection table to include a field that specifies the name of the table. But now I have broken my nice RI stuff, unless someone can figure out how to make this work:

create table XG ( cXgID c(6) primary key, cXgVgID c(6) references VG, cXgDbf c(10), cXgXxID c(6) references the-table-name-stored-in-cXgDbf )

It is that last part that the DBC doesn't want to work with. I have learned that doing things that don't fit well within the limits of the various tools (dbc, VFE, x-case, my white board...) will generally turn out to be a bad idea.

Any Sugestions?

Carl Karsten

Cross posted to comp.databases.theory, Universal Thread, VFE tech