Postpone creation of attributes until needed

[Frank Millman]

On Jun 13, 1:24 am, Steven D'Aprano
<s... at REMOVE.THIS.cybersource.com.au> wrote:
> On Tue, 12 Jun 2007 08:53:11 -0700, Frank Millman wrote:
>
> Ah, if I had ever read that there were two instances involved, I hadn't
> noticed. Sorry!
>

No problem - I really appreciate your input.

I snipped the rest of your post, as I have figured out a way to make
my problem go away, without changing my code radically.

It was a 'sequence of events' problem.

1. create table a
2. create table b, create pseudo column on table a, link to column on
table b
3. create table c, create pseudo column on table b, link to column on
table c

This works most times, but if, in step 2, I want to link the pseudo
column in table a to the pseudo column in table b, it fails, as the
pseudo column in table b does not get created until step 3.

Now, if I try to link to a column that does not exist, I set a flag
and carry on. When I go to the next step, after creating the pseudo
column, I check if the flag exists, and if so I go back and create the
link that it tried to create in the first place. It is not very
pretty, but it has the big advantage that, by the time all the tables
are opened, all the links are correctly set up, and I never have the
problem of trying to access non-existent attributes.

I will try to explain what benefit all this gives me.

Assume a Customers table and an Invoices table. Customers has a
primary key which is a generated 'next number', and an alternate key
which is the Customer Code. Users will never see the primary key, only
the alternate key.

Invoices has a foreign key CustomerId, which references the primary
key of Customers. However, when capturing an invoice, the user wants
to enter the code, not the primary key.

It is not difficult to program for this, but it is tedious to do it on
every data-entry form. Therefore, in my framework, I set up a pseudo
column on Invoices called CustomerCode, which the application
programmer can use as if it is a real column. Behind the scenes, I
automatically use that to check that it is a valid code, and populate
the real column with the primary key. This has been working for some
time, and really simplifies the 'business logic' side of things by
abstracting a common idiom which would otherwise have to be coded
explicitly every time.

Now I have added a complication. I have decided to implement the idea
of using a single table to store details of all parties with whom we
have a relationship, such as Customers, Suppliers, Agents, etc,
instead of separate tables each with their own Code, Name, and Address
details.

Therefore I now have the following- a Parties table with a 'next
number' primary key and a PartyCode alternate key, a Customers table
with a 'next number' primary key and a PartyId foreign key reference
to the Parties table, and an Invoices table with a CustomerId foreign
key reference to the Customers table.

Now when capturing an invoice, the user enters a Code, then the
program must check that the code exists on Parties, retrieve the
primary key, then check that it exists on Customers, retrieve that
primary key, then store the result on Invoices, with appropriate error
messages if any step fails. I have successfully abstracted all of
that, so all that complication is removed from the application.

Hope that makes sense.

Thanks very much for all your attempts to help me, Steven. You have
succeeded in getting me to think properly about my problem and come up
with a much cleaner solution. I really appreciate it.

Frank