🚀 go-pugleaf

Hi Simon

If I understand you correctly, in a nutshell you have a lookup table in one
table that you must use in another.  Is that right?

If I am right, here is what I did.

I have one table, call it Foods, in which the first field is a primary key
and contains autogenerated integers.  The second field in this table is
text, and it is constrained to be unique.  In a second table, one of the
fields is to represent foods and so has a many to one relationship with the
foods table (so there is a foreign key defined to enforce this).  I don't
know about you, but I tend to constrain my fields to be non null unless
there is a pressing need not to do so (and yes, I use some where such a case
can be made that null values ought to be permitted).

Now, when you need to insert data into this pair of tables, you know all the
values except for the value of the key in the lookup table.  Therefore, what
I did is create a SELECT SQL statement to get the index corresponding to the
known value (in my case, I find the index corresponding to the food value).
If the resulting recordset is empty, then I insert the food name and repeat
the SQL query to select the corresponding index (in which case, if the
insertion is necessary, gives you the highest autonumber).  You want to do
the insert ONLY if the value you're inserting isn't already in the lookup
table.  In a foods lookup table, e.g., it would be a really bad thing if the
value "orange" appeared twice in the lookup table.  Then, I have everything
I need to execute the insert into the table that uses the lookup table.  I
don't like the idea of allowing null values in the field using the lookup
table so that values can be inserted into it before knowing the index from
the lookup table because that increases the probability of error since to
insert the index later you have to make certain you're inserting into the
right record whereas with the method I used you don't have to worry about it
(as you have all the required data before you insert into this second
table).

In fact, I end up doing this so often in my application for one of the
database files I need to use (my application really needs three different
database files) that I made a function that takes the name of the lookup
table, and those of the fields, and returns the proper index.

I could be wrong, but I don't think SQL lets you insert data into a pair of
related tables with only one insert statement.  And, of course, it won't let
you insert into the table using the lookup table without having an index
already in the lookup table.  If I am mistaken on this, it is only because
my reference books on SQL don't give information on how to do this with a
single SQL statement (in fact, the main one has a whole section on how to
use multiple SQL statements to do an insert involving lookup tables).

The biggest difference between what you're doing and what I'm doing is that
I am doing everything in code using ADO/ADOX, and I have avoided using any
of the database related VCL components.  I haven't looked into using
parameterized SQL statements.  Do you have any idea how to create and use
these in code?  In my case, it MUST be done in code because the user has the
liberty to create as many database files as he likes, and I have to create
the correct structure for each database he creates.  (My application comes
with one database that is read only, and can create two kinds of database
for user data, one of which is unique and the other can be represented in as
many files as the user wishes, which will depend on how the user conceives
of relationships in his data - imagine a folder full of such files as
representing a bookshelf full of recipe books).

HTH

Ted