Here is a subset of the whole system.
In this example, the 2 Clients have 3 properties: A, B, C. Ace.A = x, Ace.B = y, Act.C = z, Acme.A = m.
Create database xCodes
Create table Client ( ;
kClient_pk int primary key, ;
cClientID c(10 ) )
Create Table xCategory (;
kxCat_PK int primary key, ;
cxCatID c(10) )
Create table xCodes (;
kxCodes_pk int primary key, ;
kClient_fk int references Client, ;
kxCat_fk int references xCategory, ;
cValue c(10) )
Insert into Client values (1, "Ace" )
Insert into Client values (2, "Acme" )
Insert into xCategory values (10, "A" )
Insert into xCategory values (11, "B" )
Insert into xCategory values (12, "C" )
Insert into xCodes values (100, 1, 10, "x" )
Insert into xCodes values (101, 1, 11, "y" )
Insert into xCodes values (102, 1, 12, "z" )
Insert into xCodes values (103, 2, 10, "m" )
select cxCatID, xCodes.cValue ;
from xCategory ;
left join xCodes ;
on kxCat_PK = xCodes.kxCat_fk and xCodes.kClient_fk = 2
This shows how to de-normalize the data into something well suited for reports. One major problem with this example is clcFieldVal builds a Field Name based on the ID that is human readable, so the field names may not be valid. One solution would be to use kxCat_PK to construct cFid1, cFid2, ... cFidN. Something I did recently was to have a xCategory.cFieldName field that stored the field name to be used. The query results were printed with an FRX that was expecting the fields to exist. I have a problem with tying dynamic data ( xCategory ) to code (the expressions in the FRX) but I think that it is a good solution. I could have created a whole FRX generating framework that would store everything in tables, but why? Besides, the FRX is a table; and a pretty appropriate table for storing FRX data in.
lcSql = ""
SET TEXTMERGE DELIMITERS TO "[[", "]]"
SET TEXT ON NOSHOW
SET TEXTMERGE TO MEMVAR lcSql
lcField = Trim(cxCatID)
\ T[[lcField]].cValue as c[[lcField]]Val
\ FROM Client
lcField = Trim(cxCatID)
\ LEFT JOIN xCodes T[[lcField]] ON kClient_pk = T[[lcField]].kClient_fk and T[[lcField]].kxCat_fk = [[kxCat_PK]]
Set Textmerge off
SET TEXTMERGE TO
? " ------------ "
lcSql = ChrTran( lcSql, Chr(13)+Chr(10), "" )
&lcSql into cursor SqlResult
Create Report xDeNorm From SqlResult
Report Form xDeNorm preview
Excellent abstraction. Some questions:
Q: How would you code many attributes into a single structured abstraction. For example: many addresses, each composed of street, city, country, zip.
A: Each Atribute/Valuse pair is stored in a seperate record in the xCode table. The example would look something like this:
Abstractly: 2 Entity recods, 3 atribute value pairs each.
Implemented using the above 5 tables: 1 in xTables, 3 in xCategories (City, State, ZIP), 3 in xTableCats (link the 3 Categories to the 1 Table), 6 xCodeValues (Niles, Anderson Island, IL...) and 6 in xCodes (link the Entitys to the xCodeValues).
- Q: How to assign a sense of priority to values? For example, say we log many phone numbers this way? How to mark one of them as the primary phone number. More generally, how do we assign some sense of sequence to repeated attribute/value pairs.
A: This is outside the scope. It is sugestion that there is more data than just this=that.
- Q: How to assign an "active" flag to attribute/value pairs?
A: Also outside scope. If this<>that, then the pair can be removed.
These are just some of the issues I've come across over the years when using abstract stuctures such as this...-- Steven Black
I think the above 5 tables solves 100% of the problem I needed to solve (stating the problem might help - coming soon). I also think that with some slight modifications it could solve 2 or 3 other problems too (and not mess up the original solution.) I think the line between "My problem" and "other problems" is in mine the values are repeated for many Entities as where it is not a good solution for values unique to a single Entity. What might work (I havn't studied this solution much at all) is to add a field to the xCode table that would allow "any" data to be entered (so no validation against the xCodeValues table.) Problem: the field really needs to be a variant datatype. This can be accomplised a varity of ways, but I have not come up with any that didn't seem fragile.
My goal is to never hear "we needed to track X so we put it in field Y because we weren't using it, and now we need to track Y also." or: "can you remove the city,st,zip from the invoice so we can use those fields to track commission info?"