Integrating three tables

[Ruslan Zasukhin]

On 12/9/05 2:30 AM, "jda" <jda at his.com> wrote:

Hi Jon,

> The new structure is basically this:
> 
> Table A
> The original db, with lots of columns
> 
> Table B -- morefields, 3 columns
> 
>    refPtr = CreateObjectPtrField("refPtr", inMyDB.Table("tableA"),
> EVOnDelete.kCascade, EVFlag.fNone)
>    moreFieldContent = CreateVarCharField("moreFieldContent", 1022,
> EVFlag.fNullable + EVFlag.fIndexed + EVFlag.fIndexByWords)
>    labelPtr = CreateObjectPtrField("labelPtr", inMyDB.Table("labels"),
> EVOnDelete.kCascade, EVFlag.fNone
> 
> 
> Table C -- labels, 1 column
>    labelName = CreateVarCharField("labelName", 1022, EVFlag.fNullable)
> 
> So my first question is, how to store data in a field in the table
> Table B in such a way that it is linked to Table A and Table C? Right
> now I'm using a simple cursor to do this in one of the fields in
> Table A:
> 
> myCursor = myDataBase.SQLselect("SELECT " + fldName + " from
> theTableA WHERE id=" + str(refNum) EVCursorLocation.kServerSide,
> EVLockType.kReadWrite)
> 
> and then using myCursor.setString (the value id is a column in Table
> A used to track records).

When you have 3 tables you can add records independently.

In the same time you can note, that in your task AT first must be added
records to TableA and TableC,

Only after this you can add record into TableB using RecIDs of above 2.

* Let you have in TableA record with RecID = 11.
    you have added it as usually

* Let you have add into TableC new record with recID = 1.

* Now to add record into TableB which links to both you can do simply

    query = "INSERT INTO TableB (refPtr, MoreFieldContents, lablePtr)
                VALUES( 11, 'something', 1 )"

    db.SqlExecute( query )




-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]