Help with Paradox 9 table schema

I'm a total newbie to Paradox and I need some help creating my
database tables. I work with Paradox 9 through Borland C++ Builder
2006. I want to have two database tables, one called Transactions and
another called TransactionDetails. Transactions has primary key
TransactionID, which is an ftString. TransactionDetails has a primary
key (TransactionID, TransactionAccountType) and both are strings.

Now, I want to make TransactionDetails be a child of Transactions so
that:

1. I cannot add a record to Transactions without first adding it to
TransactionDetails.
2. When I update a record in the Transactions table, the corresponding
record in TransactionDetails (i.e. the records with the same
TransactionID number) will also be updated. For example, if I go into
Transactions and change a record's TransactionID number from 1111 to
2222, it will automatically search in TransactionDetails for all
records that have a TransactionID number of 1111 and change those to
2222 automatically.

How do I form these two tables?

As it is now, I have C++ code that creates the two tables:

////////// CODE BEGINS HERE //////////

// create parent table
TTable* taTransactions = new TTable(this);
taTransactions->DatabaseName = "C:\\temp";
taTransactions->TableType = ttParadox;
taTransactions->TableName = "Transactions.Database";
taTransactions->FieldDefs->Clear();
taTransactions->FieldDefs->Add("TransactionID", ftString, 10, true);
taTransactions->FieldDefs->Add("TransactionDate", ftDateTime, 0,
true);
taTransactions->FieldDefs->Add("TotalAmount", ftCurrency, 0, true);
taTransactions->IndexDefs->Clear();
taTransactions->IndexDefs->Add("", "TransactionID",
TIndexOptions()<
// create child table
TTable* taTransactionDetails = new TTable(this);
taTransactionDetails->DatabaseName = "C:\\temp";
taTransactionDetails->TableType = ttParadox;
taTransactionDetails->TableName = "TransactionDetails.Database";
taTransactionDetails->FieldDefs->Clear();
taTransactionDetails->FieldDefs->Add("TransactionID", ftString, 10,
true);
taTransactionDetails->FieldDefs->Add("TransactionAccountType",
ftString, 5, true);
taTransactionDetails->FieldDefs->Add("TransactionType", ftString, 5,
true);
taTransactionDetails->FieldDefs->Add("AmountReceived", ftCurrency, 0,
true);
taTransactionDetails->FieldDefs->Add("AmountDisbursed", ftCurrency, 0,
true);
taTransactionDetails->FieldDefs->Add("AccountNumber", ftString, 8,
true);
taTransactionDetails->FieldDefs->Add("PersonID", ftString, 10, true);
taTransactionDetails->FieldDefs->Add("Percent", ftFloat, 0, true);
taTransactionDetails->IndexDefs->Clear();
taTransactionDetails->IndexDefs->Add("",
"TransactionID;TransactionAccountType", TIndexOptions()<
taTransactions->CreateTable();
taTransactionDetails->CreateTable();

// delete pointers
delete taTransactions; taTransactions = 0;
delete taTransactionDetails; taTransactionDetails = 0;

////////// CODE ENDS HERE //////////

Problem is I don't quite have it yet. There's no "link" between
Transactions and TransactionDetails. I know there's a much MUCH easier
way to do this via the Paradox 9 program instead of C++.
Unfortunately, I don't really know how to use that. Can anybody help
me?

This appears to be a C++ question, rather than a Paradox question.
Althogh you mention 'Paradox 9' (?).

While someone may be able to help you here, you may be better served
asking this on a C++ forum.

> way to do this via the Paradox 9 program instead of C++.
> Unfortunately, I don't really know how to use that. Can anybody help
> me?

Well, getting a C++ answer will be a LOT easier than starting from
scratch with Paradox the Application.


--
------------------------------
Tony McGuire

as Tonty said, these are C+ questions.. the table format isn't the issue,
it's the platform you use.. always ask questions to the people using your
native platform..

however, your game-plan has flaws, no matter what platform:


> so that I cannot add a record to Transactions without first adding it to
> TransactionDetails.

that's backwards, but I think this was a typo.. if not, it's definitely
problematic.. you *always* create the master first..


> 2. When I update a record in the Transactions table, the corresponding
> record in TransactionDetails (i.e. the records with the same TransactionID
> number) will also be updated.

serious design flaw here.. the primary key should never change.. its either
a sequential value, or a system-generated value.. it's not user-defined,
and should *never* change..

--
Steven Green - Myrtle Beach, South Carolina USA

Diamond Software Group
http://www.diamondsg.com/main.htm
Paradox Support & Sales

Diamond Sports Gems
http://www.diamondsg.com/gemsmain.htm
Sports Memorabilia and Trading Cards

>> 2. When I update a record in the Transactions table, the corresponding
>> record in TransactionDetails (i.e. the records with the same TransactionID
>> number) will also be updated.
>
> serious design flaw here.. the primary key should never change.. its either
> a sequential value, or a system-generated value.. it's not user-defined,
> and should *never* change..
>

So the concept of "referential integrity" means what exactly?

Answer: in the event that someone wants to change the primary key in a
master table, the RDBMS automatically changes the key(s) in the detail
table(s) to retain "referential integrity".

And while some people think that the primary key should not contain
meaningful data, making it meaningless is really just a convention,
partly to make it easy for RDBMS developers.

To assign meaning to keys requires some human intervention - sometimes
easier to use a sequential number, especially when lots of records are
involved.

BTW: Why do you and Tony always jump on newcomers and tell them to go
elsewhere, rather than address their issues or just staying silent?

The answer to this guy's question is for him to buy a copy of Paradox
for a few dollars, spend a few minutes reading up on how it handles
table creation and relationship, and let him get on with it.

If he wants to explain how he does things in C++ or Delphi or Esperanto,
and we can show him how to do it in a very good RDBMS for 1/4 the time
(that's why it's called a 4GL, BTW), don't we all benefit?

> BTW: Why do you and Tony always jump on newcomers and tell them to go
> elsewhere, rather than address their issues or just staying silent?

it's not "jumping on newcomers".. it's telling developers in other platforms
to go to *their* platform for the best ongoing help in *their* platform,
when they're posting code samples that must be analyzed.. if you don't do
that, even if you *can* offer a little bit of advice, you're not doing your
job to the best of your ability..

and, since you asked:

> So the concept of "referential integrity" means what exactly?

it doesn't mean that changing the primary key is generically intelligent,
even if the software will sometimes handle it for you..

> To assign meaning to keys requires some human intervention

and, in most cases, leaves you wide open to forced changes, when something
within the meaning of the key of that record changes.. the woman gets
married, the corporation gets bought out by another corporation, part
numbers need to be padded with an extra leading zero, archived data rolls
over to a new century, etc..

for the inexperienced developer, *that* is when they learn the important
lessons like this.. much better to preach generic simplicity, than to have
to explain the potential nuances to somebody that doesn't understand it
yet..

in every instance, you're not just replying to the OP.. your message is
being read by many, many other people, too.. the "lurkers" who never say a
word and/or present themselves in e-mail sometime after the original thread
passes Jupiter..

--
Steven Green - Myrtle Beach, South Carolina USA

Diamond Software Group
http://www.diamondsg.com/main.htm
Paradox Support & Sales

Diamond Sports Gems
http://www.diamondsg.com/gemsmain.htm
Sports Memorabilia and Trading Cards

"Robert Molyneux" wrote in message
news:48a96455-at-pnews.thedbcommunity.com...
>
>>> 2. When I update a record in the Transactions table, the corresponding
>>> record in TransactionDetails (i.e. the records with the same
>>> TransactionID number) will also be updated.
>>
>> serious design flaw here.. the primary key should never change.. its
>> either a sequential value, or a system-generated value.. it's not
>> user-defined, and should *never* change..
>>
>
> So the concept of "referential integrity" means what exactly?
>
> Answer: in the event that someone wants to change the primary key in a
> master table, the RDBMS automatically changes the key(s) in the detail
> table(s) to retain "referential integrity".
>
> And while some people think that the primary key should not contain
> meaningful data, making it meaningless is really just a convention, partly
> to make it easy for RDBMS developers.
>
> To assign meaning to keys requires some human intervention - sometimes
> easier to use a sequential number, especially when lots of records are
> involved.
>
> BTW: Why do you and Tony always jump on newcomers and tell them to go
> elsewhere, rather than address their issues or just staying silent?
>
> The answer to this guy's question is for him to buy a copy of Paradox for
> a few dollars, spend a few minutes reading up on how it handles table
> creation and relationship, and let him get on with it.
>
> If he wants to explain how he does things in C++ or Delphi or Esperanto,
> and we can show him how to do it in a very good RDBMS for 1/4 the time
> (that's why it's called a 4GL, BTW), don't we all benefit?
>
>

wrote:

> I'm a total newbie to Paradox and I need some help creating my
> database tables.

Why are you using Paradox tables with BC++?

The code you've shown creates the tables. Even if you figure out how to add
the Referential Integrity as part of this code, or with Paradox, you won't
get the cascading update you're looking for. I think that is the simple
answer to your question.

The only way to get this sort of behavior against Paradox tables is to
include this logic in your application behavior -- which also means you
cannot rely on it.

So my question about why Paradox is based on two facts:

(1) There are other RDBMSs that do support cascading RI -- why not use one
of them instead?
(2) The BDE and the Paradox table format are deprecated

--
Larry DiGiovanni

Steven Green wrote:

> it's not "jumping on newcomers".. it's telling developers in other
> platforms to go to *their* platform for the best ongoing
> help in *their* platform,

You can't always know they haven't done both, though. In this case, it
appears that his root problem is specific to the Paradox table architecture,
and IMHO, he did come to the right place.

> and, in most cases, leaves you wide open to forced changes, when
> something within the meaning of the key of that record changes..

Only when using an RDBMS that doesn't support RI very well, something he
know knows as a result of posting his question here.

--
Larry DiGiovanni

On Sun, 17 Aug 2008 15:12:16 -0700 (PDT), dwightarmyofchampions-at-hotmail.com
wrote:
>. There's no "link" between
> Transactions and TransactionDetails. I know there's a much MUCH easier
> way to do this via the Paradox 9 program instead of C++.
> Unfortunately, I don't really know how to use that.

What Larry said. Plus, I though I'd actually answer your question! ;-}

Yes, there is a much easier way to do this in PDox9. If you create your app
wholly within Pdox9, and abandon C++ altogether, creating tables with RI is
trivial -- but comes with *BIG* potential problems.

First, here's how you do it. Create the two tables using File/New/Tables --
the dialog is self-explanatory. Be sure to create the primary index by
putting the index fields first and putting an asterisk in front of them
(this will be clear when you see the dialog). NOW, re-open this dialog for
the subtable -- you do this with Tools/Utilities/Restructure. The far right
tab is "Referential Integrity". Use this to link it to the parent table.
That's it!

Now for the problems. Paradox is the Tounces of the RI world. It does it --
just not very well. The big problem is that the table directory paths are
HARD CODED into the structure, right down to the drive letter. If you move
either table, your whole application breaks. This makes Paradox RI
effectively undeliverable. For a one-person standalone application it does
what you want -- until you decide to buy a new computer. Then you have to
EXACTLY duplicate the drive and directory structure on the new machine. And
don'd even THINK about rearranging your hard drive.

Most of us just do RI in code, which is pretty easy once you are used to
Paradox's powerful language, OPAL, specifically structured for a RAD
database environment.

And that's the second problem: OPAL is a very odd language, and C++ by
itself will only help you a little. OPAL uses pointers a lot, much like
C++; but it is designed to look like Pascal; and it is event-driven, like
JavaScript in a browser. You cannot create new classes or objects
programmatically. Instead, you define all your objects interactively, then
hang code onto them ONLY as it is needed to modify their default behavior.
The trick is to learn the default behavior (which is VERY subtle and
complex) and only then learn what code you need to change this.

The reward: once you internalize this, Paradox is the ultimate RAD (except
for the RI problem of course). But, as both OPAL and the BDE have been
deprecated, is it worth your while? Did I mention that Paradox does not
have a Vista-ready version?

If your choice is learning Paradox from scratch vs Access or Foxpro,
Paradox is, IMHO, the better choice -- simpler and more powerful despite
its problems. But that's not your choice, is it?

--
HTH

Jim Hargan

> BTW: Why do you and Tony always jump on newcomers and tell them to
> go
> elsewhere, rather than address their issues or just staying silent?

'While you might get an answer here, you might be better served...'

If you had run into as many threads as I have with 20 answers for
Paradox when the question was for Delphi based on Paradox tables, and
no one realized it, you MIGHT just do the person, and those answering,
the favor of distinguishing between Paradox and Paradox tables as
well.

I'm not 'jumping on' them. Merely trying to help distinguish whether
they are wasting their time here when they could be asking their
question of experts in the language they are asking about.

I don't stay silent any longer because it doesn't help them.


------------------------------
Tony McGuire

> You can't always know they haven't done both, though.

true.. however, if he has gone to the Borland Builder forums, they'll shoot
down "using paradox tables" much more directly than we do.. over there,
everyone says "don't do it".. there is no mixed audience, as there is here..


> In this case, it appears that his root problem is specific to the Paradox
> table architecture, and IMHO, he did come to the right place.

also true, perhaps.. but you've done a good job of shooting down his basis,
and Jim has done a good job of showing the complexity of attempting it in
Paradox instead of Builder..

in any case, when they start by posting a bunch of code for another
platform, we *do* need to point out that the other platform's forum is the
place to assess the validity of that platform's code and concepts, even if
paradox tables are involved..


--
Steven Green - Myrtle Beach, South Carolina USA

Diamond Software Group
http://www.diamondsg.com/main.htm
Paradox Support & Sales

Diamond Sports Gems
http://www.diamondsg.com/gemsmain.htm
Sports Memorabilia and Trading Cards