Variables in standard modules, single record entry wiht foreign keys

[Ross A. Finlayson]

Hi,

I'm scratching together an Access database. The development box is
Office 95, the deployment box Office 2003.

So anyways I am griping about forms and global variables. Say for
example I'm adding a customer. The Customer fields are mostly foreign
keys that refer to primary keys in other tables, left join instead of
junction tables at this point. So, when I want to add a customer
record, I also need to add records to the other fields, eg, phone
numbers, addresses, and stuff. There are lots of fields in the table
so I split the data entry into two forms or screens, that on the
deployment fullscreen are in easy to read type.

Basically, I'm against using hidden forms, because I'd rather type
accessor and mutator, getter/setter functions/subprocedures, than draft
about bulky text fields on hidden forms. So, I use a module, say
CustomerState, that basically only has to do with editing a Customer
record or adding a Customer record. Now I had this working before by
referring to the previous screen in the forms collection, and then the
confirmation screen is presented to the user, and on OK an append-only
dynaset is run for each of the foreign keys to tag duplicates and
return the foreign keys, and stuff.

So that was great but then I am wanting to have better state management
of the forms without locking down the database, removing all the menus
and stuff, maximizing all the forms to prevent others' selection, for
basically only having one form open at a time when it's among a set of
forms containing data.

So anyways in CustomerState I defined some variables.

Option Explicit

Dim EditMode

Dim FirstName
Dim LastName
' ...

Sub CustomerState_S et_FirstName(va l)
FirstName = val
End Sub

'...

So, I got that all in there, after searching this newsgroup for
discussions about global variables and module declarations.

I change the declarations of the variables from Dim to Static, which is
wrong and crashes the application any time it's run, then Public and
then Private and then back to Public. I think the variables are
private to the module as they are only accessed through public module
functions, so I think they should be declared Private.

I was concerned that the standard module wasn't loaded with the
variables loaded before functions affecting the variables were
referenced, so I tried to call an empty procedure in the module, from
one of the afflicted forms, at that point every form module event
procedure was erroring when they were just working correctly with no
change in the immediately associated code, nor after exiting the
application and rebooting the computer. A thing I'm concerned might be
the case has to do with module compilation, and there isn't a menu item
I see to run everything as interpreted.

I set watches on the private variables, they go in and out of scope in
the functions as they should, maintaining their value, while in form
land: balk.

This is kind of aggravating me and distracting from the ease of
convenience of the rapid development of Access.

One thing I noticed and I'm not against is the suggestion to have an
error handler in each procedure or function. What should I have it do?
These global variables have a place. Say for example I have a
drop-down list for postal abbreviations of state names. It's possible
to hardcode that into each one of those lists, or have a table in the
database, where I am basically assuming in the design that the backend
tables are not to be designed with the assumption of using Access'
referential integrity, because the linked tables to the backend might
need to be changed to use a different database server. So anyways I
have a list of fifty states, where do I put it? It goes in a RecordSet
with the TableDef that is initialized the first time it's requested and
then the list refers to a dynamic recordset, built into the front end.
Also it uses the NotInList to add provinces.

With the "no hidden forms", basically these screens are modal, but
enforcing that is restrictive to the casual user/ dabblign developer.
I want the screen to be as far as matters stateless, thus with the
previous and next actually closing the form and on loading if the
fields are modified then storing the string contexts of the variants as
the value of the TextBox visual user interface components with the
basically heavyweight Form.

So I wonder: do I need to "decompile" the module(s)?

Assuming all the variables names are correct, here's something, with

Private FirstName

The variable _is_ initialized, as opposed to Dim, it's initialized, but
initialized to Empty, instead of Null. When I initialize the global
variables for the screen's use for the EditMode of either Edit or New,
for New I set all the fields on the screen/form to be Null, for Edit
these are pulled from the left join dynaset off the foreign keys, and
with setting some modified flags.

So anyways I'm rabbling here about the gross fragility of global
variables in my antique copy of Access, and I appreciate advice, don't
type Static var by itself in a module, and I hope you can tell me how
to make sure the database isn't compiled at all until deployment
testing, and stuff.

What's the deal with "On Error" and global variables? Does the
trashing of the stack only occur with errors that stop the processing,
or are there a bunch of subtle errors that happen all the time without
telling me? Oh...

What I'm interested in here are global variables, just one is plenty,
that work all the time. If you can send a few couple paragraphs
explaining the essence of the error handling and the stack and stuff,
or otherwise solving my problem, I'd really appreciate it.

Thank you.

Ross F.

[Steve Jorgensen]

It sounds like there's are one or more genuine, interesting questions encoded
somewhere in there, but I don't have time to dig them out. Can you organize
your thoughts a little, and try again?

On 11 Apr 2005 07:17:41 -0700, "Ross A. Finlayson" <ra*@tiki-lounge.com>
wrote:

Hi,

I'm scratching together an Access database. The development box is
Office 95, the deployment box Office 2003.

So anyways I am griping about forms and global variables. Say for
example I'm adding a customer. The Customer fields are mostly foreign
keys that refer to primary keys in other tables, left join instead of
junction tables at this point. So, when I want to add a customer
record, I also need to add records to the other fields, eg, phone
numbers, addresses, and stuff. There are lots of fields in the table
so I split the data entry into two forms or screens, that on the
deployment fullscreen are in easy to read type.

Basically, I'm against using hidden forms, because I'd rather type
accessor and mutator, getter/setter functions/subprocedures, than draft
about bulky text fields on hidden forms. So, I use a module, say
CustomerStat e, that basically only has to do with editing a Customer
record or adding a Customer record. Now I had this working before by
referring to the previous screen in the forms collection, and then the
confirmation screen is presented to the user, and on OK an append-only
dynaset is run for each of the foreign keys to tag duplicates and
return the foreign keys, and stuff.

So that was great but then I am wanting to have better state management
of the forms without locking down the database, removing all the menus
and stuff, maximizing all the forms to prevent others' selection, for
basically only having one form open at a time when it's among a set of
forms containing data.

So anyways in CustomerState I defined some variables.

Option Explicit

Dim EditMode

Dim FirstName
Dim LastName
' ...

Sub CustomerState_S et_FirstName(va l)
FirstName = val
End Sub

'...

So, I got that all in there, after searching this newsgroup for
discussions about global variables and module declarations.

I change the declarations of the variables from Dim to Static, which is
wrong and crashes the application any time it's run, then Public and
then Private and then back to Public. I think the variables are
private to the module as they are only accessed through public module
functions, so I think they should be declared Private.

I was concerned that the standard module wasn't loaded with the
variables loaded before functions affecting the variables were
referenced, so I tried to call an empty procedure in the module, from
one of the afflicted forms, at that point every form module event
procedure was erroring when they were just working correctly with no
change in the immediately associated code, nor after exiting the
application and rebooting the computer. A thing I'm concerned might be
the case has to do with module compilation, and there isn't a menu item
I see to run everything as interpreted.

I set watches on the private variables, they go in and out of scope in
the functions as they should, maintaining their value, while in form
land: balk.

This is kind of aggravating me and distracting from the ease of
convenience of the rapid development of Access.

One thing I noticed and I'm not against is the suggestion to have an
error handler in each procedure or function. What should I have it do?
These global variables have a place. Say for example I have a
drop-down list for postal abbreviations of state names. It's possible
to hardcode that into each one of those lists, or have a table in the
database, where I am basically assuming in the design that the backend
tables are not to be designed with the assumption of using Access'
referential integrity, because the linked tables to the backend might
need to be changed to use a different database server. So anyways I
have a list of fifty states, where do I put it? It goes in a RecordSet
with the TableDef that is initialized the first time it's requested and
then the list refers to a dynamic recordset, built into the front end.
Also it uses the NotInList to add provinces.

With the "no hidden forms", basically these screens are modal, but
enforcing that is restrictive to the casual user/ dabblign developer.
I want the screen to be as far as matters stateless, thus with the
previous and next actually closing the form and on loading if the
fields are modified then storing the string contexts of the variants as
the value of the TextBox visual user interface components with the
basically heavyweight Form.

So I wonder: do I need to "decompile" the module(s)?

Assuming all the variables names are correct, here's something, with

Private FirstName

The variable _is_ initialized, as opposed to Dim, it's initialized, but
initialized to Empty, instead of Null. When I initialize the global
variables for the screen's use for the EditMode of either Edit or New,
for New I set all the fields on the screen/form to be Null, for Edit
these are pulled from the left join dynaset off the foreign keys, and
with setting some modified flags.

So anyways I'm rabbling here about the gross fragility of global
variables in my antique copy of Access, and I appreciate advice, don't
type Static var by itself in a module, and I hope you can tell me how
to make sure the database isn't compiled at all until deployment
testing, and stuff.

What's the deal with "On Error" and global variables? Does the
trashing of the stack only occur with errors that stop the processing,
or are there a bunch of subtle errors that happen all the time without
telling me? Oh...

What I'm interested in here are global variables, just one is plenty,
that work all the time. If you can send a few couple paragraphs
explaining the essence of the error handling and the stack and stuff,
or otherwise solving my problem, I'd really appreciate it.

Thank you.

Ross F.

[Ross A. Finlayson]

Steve Jorgensen wrote:

It sounds like there's are one or more genuine, interesting questions encoded somewhere in there, but I don't have time to dig them out. Can you organize your thoughts a little, and try again?

On 11 Apr 2005 07:17:41 -0700, "Ross A. Finlayson" <ra*@tiki-lounge.com> wrote:

Hi,

I'm scratching together an Access database. The development box is
Office 95, the deployment box Office 2003.

Hi Steve,

Probably not, no. Heh heh heh.

I solved a lot of my problems by actually compiling all modules. That
actually goes through and finds type mismatches and stuff, where I was
mistaken that the code was correct. It seems important to correct all
the errors revealed by compiling the modules.

About Dim and Private, in terms of declaring variables, in the module
Private is a synonym for Dim, variables declared with Dim are Private
by default, if you want to access them directly it is required to have
them be declared Public.

It is not probably a good idea to try and use the variables directly
from particularly expressions and also other modules. Instead, use the
accessor functions, getter/setters, which basically have to do with the
notion of data hiding in object-oriented languages.

Dim FirstName As Variant

Now, the "As Variant" is unnecessary, because unless you change the
default type of variables, it is Variant. The Variant is kind of like
a C-language union, the Variant, there are some fixty or sixty types of
things that it can be interpreted as, from integers to BSTR, or "OLE
strings", DynArrays, SafeArrays, and various other forms of arrays,
BLOBs or Binary Large Objects, and stuff. So anyways as a variable the
Variant doesn't have a default constructor, so you can't say

Dim FirstName As New Variant

Thus initializing it to something other than empty. Instead, I have
one function setting all the variables in the record state to Null for
the Add Record, for simple text fields with no default values, and to
the results of the query for editing an existing record with the same
forms, with the word "New" replaced with "Edit" in the caption of the
form.

Now the bedugger, or rather, debugger window really helped me solve
some problems. With a module open, hit Ctrl-G, learn the syntax of the
expression identifiers, and it's really handy. Another suggestion I
read from Dinh was to enable all errors in the Options Modules tab.

So, I appear to have solved the bulk of my particular problem.

While that is so, say now the Customer record is referenced from a
different record with its own data entry screens, and then I want to
link them together without recoding. Or, how about junction tables,
basically I'm trying to figure out many to many, I guess that's what
form and report sections and GROUP BY queries are for.

Here's one, say I have a table with mailing addresses in it.

AddressLine1
AddressLine2
AddressLine3
AddressCity
AddressState
AddressPostal
AddressCountry

Now those are address lines and not the addressee line, like

Mailstop 2000
Rt 4e 7-a
Eastern Division
New York, NY 20050-2000
USA

Now besides the fact that that's probably not an actual postal address,
the next address is probably more along the lines of

32 Cherry Lane
Sticks, Nebraska 34534

Now, when I display that record on a form or report, it looks like I
have to concatenate the string with new lines discarding empty lines in
a multiline text box. This is where the table for mailing, shipping,
and billing addresses just contains an identifier, a foreign key mapped
to the primary key of the addresses table, because for most individuals
they're the same thing.

That's not really a problem, I just wonder how it's done. In figuring
out good editor metaphors for not just flat-linked tables but also the
complex right and left, inner and outer joins, junction tables and even
worse circular references, while at least manageable from a design
perspective in terms of identifying the referential integrity
constraints, designing an intuitive user interface to work with the
arbitrarily related data leads to a variety of considerations and
concerns.

Here's another thing: I'm updating a record without locking it, I need
to figure out how to lock the record from not necessarily reads, but
other writes. Access will do that automatically, and is pretty good
about that, but I'm not using the table validation either, instead
using functions to validate and if validated regularize.

That leads into a good idea: have as little code as possible in the
form modules, try and offload as much of the code as as is feasible
into the standard or library modules. For stuff that's only happening
on the form that's kind of pointless, but if I can identify reused
blocks of fields, then it would seem right to give them the same
identifiers and pass the form to a library function. Don't use massive
queries, make a big query with everything for a table brought in, and
then where you use it, make a copy of the query and cut out all the
tables you don't need. It's better, in a way to use a custom query
from the queries (quairies, queeries, potato, potato) because then if
you later move the linked tables off to a server then you can convert
the queries to stored procedures. About utility tables that aren't to
actually be tables in the database and might not need to be used every
time a form is loaded, the idea of dynamically generating with the DAO
style table definitions might be useful, I haven't tried it.

I first picked up Access around ten years ago, Access 2.0 on Windows
for Workgroups 3.11. I haven't used it steadily since, in fact it's
been six or seven years. In retrospect it's a very productive tool
because just out of college and not a computer science major much was
accomplished, I think my first Access database was all macros and is to
this day. The one I build now has zero macros, although I hear there's
something about the accelerator keys, but today I could write a COM
component in assembler, although that would take a solid week at least.

Databases, the 4GL: while basically graphs, (circuits, networks), I
would like to read some articles about designing effective and
high-performance user interfaces for complex datasets.

I guess I'm just more used to compiled languages. That may soon
change.

Ross F.

[Steve Jorgensen]

On 11 Apr 2005 15:39:32 -0700, "Ross A. Finlayson" <ra*@tiki-lounge.com>
wrote:

Steve Jorgensen wrote:

It sounds like there's are one or more genuine, interesting questionsencoded

somewhere in there, but I don't have time to dig them out. Can you

organize

your thoughts a little, and try again?

On 11 Apr 2005 07:17:41 -0700, "Ross A. Finlayson"

<ra*@tiki-lounge.com>

wrote:

>Hi,
>
>I'm scratching together an Access database. The development box is
>Office 95, the deployment box Office 2003.
>

Hi Steve,

Probably not, no. Heh heh heh.

I solved a lot of my problems by actually compiling all modules. That
actually goes through and finds type mismatches and stuff, where I was
mistaken that the code was correct. It seems important to correct all
the errors revealed by compiling the modules.

About Dim and Private, in terms of declaring variables, in the module
Private is a synonym for Dim, variables declared with Dim are Private
by default, if you want to access them directly it is required to have
them be declared Public.

No - Dim and public ar the same. If you want variables to be Private, you
must be explicit. Actually, I never use Dim because Dim means I've never
considered whether the variable should be public or private, and I don't know
whether to expect side effects when I change its value.
It is not probably a good idea to try and use the variables directly
from particularly expressions and also other modules. Instead, use the
accessor functions, getter/setters, which basically have to do with the
notion of data hiding in object-oriented languages.
Sometimes yes - sometimes no. In Access, there's no difference from the
calling code's perspective between a public member variable and a public
member using Propery Get, Let, or Set. So long as it is appropriate for
external code to both change andn read the property at will, and the class
doesn't need to take immediate action in response to a value change, getters
and setters are not needed. You can add the getters and setters in the cases
when they do become needed. Eventually, that will be most of them, but still,
it's fine to wait until the need presents itself in each case.
Dim FirstName As Variant

Now, the "As Variant" is unnecessary, because unless you change the
default type of variables, it is Variant. The Variant is kind of like
Unnecessary so long as you don't care whether it looks like you decided what
type it should be or not. Saying As Variant shows that you did want it to be
a variant, and did not just forget to specify the type.
a C-language union, the Variant, there are some fixty or sixty types of
things that it can be interpreted as, from integers to BSTR, or "OLE
strings", DynArrays, SafeArrays, and various other forms of arrays,
BLOBs or Binary Large Objects, and stuff. So anyways as a variable the
Variant doesn't have a default constructor, so you can't say

Dim FirstName As New Variant

Thus initializing it to something other than empty. Instead, I have
one function setting all the variables in the record state to Null for
the Add Record, for simple text fields with no default values, and to
the results of the query for editing an existing record with the same
forms, with the word "New" replaced with "Edit" in the caption of the
form.

Now the bedugger, or rather, debugger window really helped me solve
some problems. With a module open, hit Ctrl-G, learn the syntax of the
expression identifiers, and it's really handy. Another suggestion I
read from Dinh was to enable all errors in the Options Modules tab.

So, I appear to have solved the bulk of my particular problem.

While that is so, say now the Customer record is referenced from a
different record with its own data entry screens, and then I want to
The use of the term "record" here is confusing. Do you mean another form
that's bound to a different record?
link them together without recoding. Or, how about junction tables,
basically I'm trying to figure out many to many, I guess that's what
form and report sections and GROUP BY queries are for.

Now, you've gone from variable types and scope to queries without ever saying
what you're really trying to accomplish in any understandable way.

Not to be brusque, but I'm done with this message for now. I might look at
the rest of it another time. It's always helpful, when asking for advice, to
start with "what" you are trying to do in a fairly limited part of the problem
scope, then if you're trying an approach, say "how" you are trying to do it.
Finally, say what trouble or confusion you are having with that approach. If
you have multiple distinct issues, make multiple posts. It also helps to try
to make sure you're using the right terminology for all the things you're
referring to, so we're all speaking the same language (or ask if unsure).

[Ross A. Finlayson]

Hi,

Oh, I thought the documentation stated that a module variable, in the
declarations section of the module, declared with "Dim" is the same as
declared "Private", and that instead to make it public the explicit
"Public" modifier would need to be used.

About a record, I mean a record from another table. Say for example
there's an Account type, and it has multiple customers. In adding or
editing an Account record, there might be an AccountCustomer s junction
table,

AccountCustomer s_ID
Account_ID
Customer_ID

Then, in adding customers to the account, sometimes the customer exists
and so it can be selected from the current customers table populating a
drop-down box, otherwise a new customer should be added. The customer
is a complex record, so its own screens are launched to add the
customer record, and then when they're done the entire purpose of
adding a new customer record is to go into the account's
account/customer junction table.

So, in trying to make the forms "stateless" , which might be an oxymoron
or should be "stateful", where for adding a Customer in multiple
screens only one of those forms is even open at a time, with the
"previous" and "next" opening that previous or next form, and closing
the current form, with updates on the form fields modifying the global
variables, part of the idea with that is that none of the controls on
the add/edit forms are bound to any table or query, they only reflect
the variables of a record/table row, that are off in the record's
utility function module.

Now a problem with that is when the screens are for editing instead of
adding, then a record is loaded and the variables are set to those
record's values and the record (query result) is closed. In a
multi-user system, I need to figure out a way to lock that record, and
on hitting the "OK" button on the "Confirm Edit" screen, or "Cancel" on
any of the other screens using for the record data, to remove the lock.

The recordset is closed after being used to set the variable state for
the record edit session, so the automatic "optimistic " (page) and
"pessimisti c" (record?) locks should be removed, because the query
represents records from multiple tables. Then I read there are
"record" locks in Access 2000, and basically my sphere of knowledge
about locking is a ball of confusion.

http://www.microsoft.com/ACCESSDEV/Articles/jetlund.htm :

"There are seven types of Microsoft Jet locks:

User locks
Write locks
Read locks
Commit locks (Microsoft Jet 2.x only)
Table-read locks
Table-write locks
Table deny-write locks"

"Note that the names of these locks were assigned by the Microsoft Jet
development team and do not necessarily have the same meaning that
other database vendors use."

I need to figure out when locks will prevent reads as well as writes,
and how to implement my own locking strategy. When I'm editing an
Account record, and not actually editing a Customer record, only
possibly editing the foreign Customer_ID key in the Account table, I
don't want to have locked that Customer_ID primary key's record in the
Customer table.

Now, here I have a separate type of question, about basically a comment
type field. Say I want to have comments for Accounts, and comments for
Customers. Then I could have a comment table

Comment_ID
Comment_Text

Then if there are to be multiple comments per account or customer, then
each would need a separate junction table.

AccountComments :
AccountComment_ ID
Account_ID
Comment_ID

CustomerComment s:
CustomerComment _ID
Customer_ID
Comment_ID

I wonder about good ways to do that, basically about having metadata on
each of the tables, with possibly multiple (arbitrarily many, 1:Many,
1:M, 1:N) fields of metadata.

Another thing I'm trying to consider is a good way to have, basically
as part of metadata, an InactiveRecord flag. That's about, say, an
inactive Customer, where they aren't necessarily to be removed from the
database. One idea is to have an "InactiveCustom er" table, and move
inactivated customers to that table, removing them from the default
active Customers table. Another is to have a yes/no field in the
Customer table for Active, but that would require every query to filter
on that field, instead of the other way of using a union query when the
otherwise inactive records are to be included.

I guess these are just some examples of basically pedestrian and
rudimentary database design considerations. While that's so, it's good
to have some grasp of these kinds of notions, of basically record
modification design patterns, and now some notion of different types of
record metadata.

Thanks for having helped with some feedback, I don't have any
_specific_ questions per se, just more of _vague_ ones.

Ross F.

[Ross A. Finlayson]

Hi,

I'm trying to figure out how to add comments to stuff.

Say I have tables along the lines of

Contact <- individual
Company <- business entity, might be a customer or vendor with contacts
Customer <- might be a contact, or company
Vendor <- might be a contact, of company, might also be a customer

Then, say I want to have comments about those things stored in the
database.

Comment table:
Comment_ID
Comment_Text

Then, I want to associate the comment with various things. For
example, say the comment is about a contact, or about any contact that
is the contact of a vendor, or about a customer, or about an order, or
an invoice, or the invoicing process of a customer, etcetera, etcetera.
Basically I get to looking at the Cartesian product of all the tables
besides comment and having a comment or multiple comments for each
entry in the product.

The comments might be really sparse, say there's a Customer table, if
it only has four entries or something, while I'm ignorant about the
internal storage of that, as a flat record if I add a 4 kilobyte
comment extension then it is pretty obvious that instead having a left
outer join by the comment foreign key to the comment table would be
better. Then again I wonder about that, because there might be multipe
comments, leading to either having a specialized comment table for each
contact, customer, vendor, company, etcetera, or junction tables
dealing with a centralized comment table, which might have only one of
its many entries about a given table.

Another problem is the comments get into which users should be able to
read and write them, or add or delete. This is besides concepts of
storing records of any modification, adds, and deletes, and when they
happened and who did them.

Comment_ID
Comment_Text
Comment_ModifyG roup
Comment_AddGrou p
Comment_DeleteG roup
Comment_ModifyD ate <- multiple records
Comment_AddDate
Comment_DeleteD ate
These group, date, and activity fields are basically representative of
some vague notion of having those fields in every table, eg, having for
every table those fields, with "deleted" records moved to a "deleted"
table, which is separate from the "inactive" table, where those are
unioned with the "active" table which is itselfused to populate regular
working forms and so on.

That leads to the notion of having every version of a table row's
contents stored in the database.

Obviously a user could just delete a row and it would be gone, but the
idea is to have record deletion, and modification, basically insulated
from the user towards that deletes actually just copy the record to the
"deleted" table, eg Comment_Deleted , with setting the date when it was
deleted. Then, for modifications, the previous state of the record
should be mapped somehow.

Comment_Modific ation:
Comment_Modific ation_ID <- primary key
Comment_ID
Comment_Text
Comment_Prev_Mo difyDate

(There should only be one primary key field in the table.)

So, on a modification of a comment, it should be copied into the
Comment_Modific ation table as a new entry, and so on and so forth for
any of the other tables, besides the meta tables.

Basically I'm trying to figure out, say I can cobble together the table
and relationships for business relationships, then I want to
automatically generate the comment type fields, multi-user record
modification storage, other sparse metarecords on the Cartesian product
of all non-meta tables. So, for Access I would like to have an Add-In
or export the table definitions and have automatic generation of the
table specifications for these vague meta-data type things.

The business relations, while pretty much straightforward , and kind of
sticky to model in terms of varying roles. There are some
considerations of circularity.

This metadata, basically I write function to, for example, AddRecord.
For example, AddContact, it returns the ID of the contacts just added,
and its arguments are variants that can be null.

AddContact(firs t, last, middle, salutation, phoneid, altphoneid, faxid,
altfaxid, emailid, altemailid, homepageid, billid, shipid, mailid)

Now those arguments that are IDs are from adding records to the phone
number table. For example, where the billing, shipping, and mailing
addresses are all the same, the AddRecord function detects duplicates
and returns the same ID for each, and then each is passed to the
AddContact. While that is so, what if the contact has 500 alternate
phone numbers? That's a rare case, and trying to design around it is
probably counterproducti ve. Let's say there's a company, though,
besides the primary contacts for the company, there might be a bunch of
other ones who are to be on the holiday mailing, or there should be
stored references to their dietary restrictions, whatever, basically
that leads to expansion of types, and too much information.

The comments, they might be ephemeral, just phone call note or
something, although that gets into individual phone calls, and whether
they're billable, but maybe if the comment has no action taken, after a
week it expires or something. Basically the comment is like a
sticky-note, except, there are various kinds of comments and while an
actual sticky-note metaphor might work as a visual metaphor for some,
for others it does not. Basically leading into annotations and
annotated records, that leads to a lot of ancillary tables for each of
the tables that contains the meaningful business data.

I guess if those can be determined readily, then an add-in type deal, a
code generator, could generate for each table T say

T_Inactive
T_Deleted
T_Comment_Junct ion

And then, there are the notions of implementing AddRecord functions, by
the type and whether the field indicates a foreign key, basically by
ending its name with ID, or as representative of a foreign key to a
specific other table's primary key, Foreign_ID, generating ancillary
metadata utility tables and functions.

Contact:

....
Primary_PhoneNu mber_ID
Alternate_Phone Number_ID
Fax_PhoneNumber _ID
Cellphone_Phone Number_ID
....

PhoneNumber:
PhoneNumber_ID
PhoneNumber_Are aCode
PhoneNumber_Exc hangeAndNumber
PhoneNumber_Int ernationalDiali ngCode

But then, why not

PhoneNumber:
PhoneNumber_ID
PhoneNumber_Typ e <- primary, alternate, fax, cell, other, enumerated
....

with a junction table on the contact or other thing with phone number?
On the visual entry form for a contact, their phone and fax are just
typed in by the person doing the data entry, and adding, displaying,
editing, and using that information gets complicated.

That's one problem with the junction tables, the identifier range is
basically 32 bits, and while with a function to generate identifiers
(IDs), those could be put in separate ranges so a junction table could
apply to more than one table on the left side by their identifiers, the
autonumber field, well, I guess it can be set to start at a given
value.

The database is basically a persistent store, for complicated data
structures each of the tables is containing pieces of the data
structures. It's like serializing trees to flat memory, understanding
the logic and specifying the pointers among the data by their
identifiers.

I guess it's a matter of figuring out a little domain specific language
(little language or DSL) that I can use myself to compile these
slightly higher level constructs over the data structures of the
database: the tables and relational joins or queries. Then, the forms
need to be in place so the user alternately fills entry boxes by typing
on the keyboard and list selection, and hitting tab and return at the
end of the page which fills the screen maximized, for adding and
editing records, in a multi-user environment, with a design that is
readily migrated to different back ends or front ends, or both.

It's convenient in that in this case this is being done in the actual
design stages of the database where there is no data. With modifying a
database that already contains zillions of records, that would lead to
many more concerns about changing the way things are done, which is why
design can take a long time, but not an unreasonable amount of time.

Ross