Re: Postpone creation of attributes until needed

On Jun 13, 1:24 am, Steven D'Aprano
<s...@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> 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

.

Relevant Pages

  • RE: Sync 3 related tables on a form
    ... You can try to maintain a multi query join string for the same purpose. ... Create a primary key in the Customers table. ... Create a relationship between the Orders table and the OrderDetails ...
    (microsoft.public.vb.database)
  • RE: How we get record from two database and insert into other database
    ... Create a primary key in the Customers table. ... Create a relationship between the Orders table and the OrderDetails ... The data type is string for this element, ...
    (microsoft.public.vb.database)
  • Re: Primary Key Dilemma
    ... > I'm designing an application that will be sold to multiple customers, each> of which is a small business. ... The database behind this application will be> accessed via the Internet, and will be shared by *all* customers. ... Each> business is permitted to access only its own data. ... It just doesn't seem right to me to have an auto inc column AND a> separate business_id column comprising the primary key. ...
    (microsoft.public.sqlserver.programming)
  • Re: Using DataAdapter.Fill()
    ... Here is the description from the documentation for AddWithKey: ... Adds the necessary columns and primary key information to complete the ... to put a 'distinct' in your query to retrieve each customer just once. ... Orders & Customers which are related by a CustomerID. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Best practice kinda question
    ... Was the computed totals column in invoices redundant or do you really ... (cust_id INTEGER NOT NULL PRIMARY KEY, ... REFERENCES Companies, ... product_id INTEGER NOT NULL, -- refs Inventory ...
    (microsoft.public.sqlserver.programming)