Multiple Inserts across multiple tables

On 3 June, 16:25, Jerry Stuckle wrote:
> Lennart Jonsson wrote:
> > On 2010-06-03 16:34, David wrote:
> >> Hi Guys,
>
> >> After a bit of advice really.
>
> >> We have a number of tables in our database, that when a new account is
> >> created it should insert into all of them (different information) get
> >> the insert ids from all and the update the first table with all the
> >> ids (probably not the best way to do it, so if anyone can point me in
> >> the right direction, feel free).
>
> > I don't understand your data model. Why do you need information
> > regarding client_company_name in the client_account table? A typical
> > model for what you describe (at least how I interpret it) would look like:
>
> <snip unrelated info>
>
>
>
> > /Lennart
>
> > [...]
>
> It's quite common to have multiple clients with one company.  Different
> departments, for instance, may have separate accounts.  But billing
> still goes to the same place.
>
> And each account may have one or more users authorized to use the
> account; each user would have to be inserted.
>
> But in general, I do agree with you - from the skimpy description, I
> don't think this sounds like a good data model.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck....DeleteThis@attglobal.net
> ==================

Thanks for the input so far.

I kept the company name out of the client_account originally so that
everything was small int() fields, but as the database grew and other
options needed char fields did start creeping in.

The way our database works currently is, we have clients who have
multiple addresses (same company name though), we have resellers who
have multiple addresses (same company name) but who can place orders
for their clients, which must also have an account with us. so we can
get multiple addresses linked to different accounts (ie client and
reseller)

I will paste all tables which link to the client account table, as
Jerry has suggested, my original post only showed a few.

CREATE TABLE `client_account` (
`id` int(15) NOT NULL auto_increment,
`acc_id` char(15) NOT NULL,
`address_id` int(5) NOT NULL,
`cname_id` int(5) NOT NULL,
`acontact_id` int(5) NOT NULL,
`scontact_id` int(5) NOT NULL,
`tcontact_id` int(5) NOT NULL,
`atel_id` int(5) NOT NULL,
`stel_id` int(5) NOT NULL,
`ttel_id` int(5) NOT NULL,
`afax_id` int(5) NOT NULL,
`sfax_id` int(5) NOT NULL,
`tfax_id` int(5) NOT NULL,
`sales_emailaddress_id` int(5) NOT NULL,
`accounts_emailaddress_id` int(5) NOT NULL,
`technical_emailaddress_id` int(5) NOT NULL,
`live` enum('N','Y') NOT NULL default 'Y',
`exempt` enum('Y','N') NOT NULL,
`reseller` enum('Y','N') NOT NULL default 'N',
`payment_terms` int(3) default NULL,
`reseller_link_id` char(12) default NULL,
`invoice_out_note` char(50) NOT NULL,
`date` date default NULL,
`suspend` enum('N','Y') NOT NULL default 'N',
`reputation` enum('N','Y') NOT NULL default 'Y',
`uid` int(5) NOT NULL default '6',
PRIMARY KEY (`id`),
UNIQUE KEY `acc_id` (`acc_id`)
)

CREATE TABLE `client_address` (
`id` int(3) NOT NULL auto_increment,
`address` text,
`add2` char(150) default NULL,
`town` char(100) NOT NULL,
`county` char(100) NOT NULL,
`postcode` char(10) NOT NULL,
`country_id` int(4) NOT NULL default '222',
`type` int(5) default NULL,
`acc_id` int(11) default NULL,
`uid` int(5) NOT NULL default '6',
PRIMARY KEY (`id`),
KEY `town` (`town`),
KEY `county` (`county`)
)

CREATE TABLE `client_company_name` (
`id` int(3) NOT NULL auto_increment,
`acc_id` int(5) NOT NULL,
`account` char(12) default NULL,
`companyname` char(100) default NULL,
`live` enum('N','Y') NOT NULL default 'Y',
`suspend` enum('N','Y') NOT NULL default 'N',
`uid` int(11) NOT NULL default '6',
PRIMARY KEY (`id`),
KEY `account` (`account`),
KEY `acc_id` (`acc_id`)
)

CREATE TABLE `client_security_details` ( // 1 row is inserted by
default for security details, but others are added for each product
ordered.
`id` int(5) NOT NULL auto_increment,
`acc_id` int(5) default NULL,
`details` text,
`type` enum('Company','Reseller','Client') default NULL,
PRIMARY KEY (`id`)
)

CREATE TABLE `client_invoice_settings` ( // 1 row is inserted by
default, but others are added for each product ordered.
`id` int(11) NOT NULL auto_increment,
`aid` int(11) default NULL,
`package_id` int(15) NOT NULL,
`cca` enum('N','Y') default 'N',
`early_invoicing` tinyint(2) NOT NULL,
`standing_order` enum('N','Y') default NULL,
`area` enum('Account','Reseller','Client') NOT NULL default
'Account',
PRIMARY KEY (`id`),
KEY `package_id` (`package_id`)
)

CREATE TABLE `client_contacts` ( // Insert 3 rows, (account, sales,
technical)
`id` int(5) NOT NULL auto_increment,
`acc_id` int(5) NOT NULL,
`contact` char(200) NOT NULL,
`type` int(5) NOT NULL,
`live` enum('N','Y') NOT NULL default 'Y',
`uid` int(5) NOT NULL default '6',
PRIMARY KEY (`id`),
KEY `type` (`type`)
)

CREATE TABLE `client_email_address` ( // initially insert 3 rows,
(account, sales, technical) but more can be added for each
`id` int(5) NOT NULL auto_increment,
`acc_id` int(5) NOT NULL,
`email` varchar(200) NOT NULL,
`area` enum('Sales','Accounts','Technical') NOT NULL default
'Sales',
`live` enum('N','Y') NOT NULL default 'Y',
`uid` int(5) NOT NULL default '6',
PRIMARY KEY (`id`),
KEY `area` (`area`)
)

CREATE TABLE `client_numbers` ( // 6 added by default, 3 for
telephone, 3 for fax. but more can be added later.
`id` int(5) NOT NULL auto_increment,
`acc_id` int(5) NOT NULL,
`number` char(20) default NULL,
`area` enum('Sales','Accounts','Technical') NOT NULL default
'Sales',
`type` enum('Tel','Fax') NOT NULL default 'Tel',
`live` enum('N','Y') NOT NULL default 'Y',
`uid` int(5) NOT NULL default '6',
PRIMARY KEY (`id`),
KEY `type` (`type`)
)

Then we update the original table (client_account) with the insert
ids.

When a reseller orders something on behalf of a client who does not
exist in our database yet, the order details are placed with resellers
account id, which means the company name cannot link directly to their
account id. (or at least thats how i understand it).

Thanks
Dave.
 >> Stay informed about: Multiple Inserts across multiple tables 

On 2010-06-03 16:34, David wrote:
> Hi Guys,
>
> After a bit of advice really.
>
> We have a number of tables in our database, that when a new account is
> created it should insert into all of them (different information) get
> the insert ids from all and the update the first table with all the
> ids (probably not the best way to do it, so if anyone can point me in
> the right direction, feel free).
>

I don't understand your data model. Why do you need information
regarding client_company_name in the client_account table? A typical
model for what you describe (at least how I interpret it) would look like:

create table client_account (
client_account_id integer not null primary key,
...
) engine=innodb;

create table client_account_name (
...,

client_account_id integer not null,
...,
companyname char(100) not null,
...
foreign key ( client_account_id )
references client_account ( client_account_id )
) engine=innodb;

to retrieve information regarding client_account and the name of the
company:

select x.client_account_id, y.companyname
from client_account x
join client_account_name y
on x.client_account_id = y.client_account_id

Why is this model not an option for you? I'm not saying that it will fit
for you, but you will have to explain why it does not.

> What I want to know is, if one of the inserts failed, is it possible
> to rollback all the other inserts. I have read a few articles and
> believe its possible if using InnoDB, but not sure about MyISAM.

Not possible with MyISAM, neither is foreign keys. If possible, and the
data is important to you, consider moving to InnoDB. You will gain
referential integrity and acid (at least I think innodb is acid
compliant, haven't checked), which will spare you a lot of logic that
otherwise have to be coded in the application. I this particular case
you will have to implement undo operations for your insert/update/delete
operations. This you will have to do in every application that modifies
your data, not to mention the interesting effects a trigger will have on
your application logic ...


/Lennart

[...]
 >> Stay informed about: Multiple Inserts across multiple tables 

Lennart Jonsson wrote:
> On 2010-06-03 16:34, David wrote:
>> Hi Guys,
>>
>> After a bit of advice really.
>>
>> We have a number of tables in our database, that when a new account is
>> created it should insert into all of them (different information) get
>> the insert ids from all and the update the first table with all the
>> ids (probably not the best way to do it, so if anyone can point me in
>> the right direction, feel free).
>>
>
> I don't understand your data model. Why do you need information
> regarding client_company_name in the client_account table? A typical
> model for what you describe (at least how I interpret it) would look like:
>
<snip unrelated info>
>
> /Lennart
>
> [...]


It's quite common to have multiple clients with one company. Different
departments, for instance, may have separate accounts. But billing
still goes to the same place.

And each account may have one or more users authorized to use the
account; each user would have to be inserted.

But in general, I do agree with you - from the skimpy description, I
don't think this sounds like a good data model.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex.TakeThisOut@attglobal.net
==================
 >> Stay informed about: Multiple Inserts across multiple tables 

On 2010-06-03 17:25, Jerry Stuckle wrote:
[...]
>
>
> It's quite common to have multiple clients with one company. Different
> departments, for instance, may have separate accounts. But billing
> still goes to the same place.
>
> And each account may have one or more users authorized to use the
> account; each user would have to be inserted.
>

All agreed, but I don't think I would end up with a data model like the
OP's, no matter what However, I think you are making an important
point here. It's essential that the business rules and the conceptual
model are well defined (at least the parts we are trying to implement),
before we start writing ddl.

/Lennart

[...]
 >> Stay informed about: Multiple Inserts across multiple tables 

On Jun 3, 7:28 pm, Lennart Jonsson
wrote:
> On 2010-06-03 17:25, Jerry Stuckle wrote:
> [...]
>
>
>
> > It's quite common to have multiple clients with one company.  Different
> > departments, for instance, may have separate accounts.  But billing
> > still goes to the same place.
>
> > And each account may have one or more users authorized to use the
> > account; each user would have to be inserted.
>
> All agreed, but I don't think I would end up with a data model like the
> OP's, no matter what
An author who likes playing with language (e.g. Terry Pratchett or
Douglas Adams) would say something like:

"There is no such thing as a perfect data model and this is an
excellent example."
 >> Stay informed about: Multiple Inserts across multiple tables 

On 4 June, 09:27, Captain Paralytic wrote:
> On Jun 3, 7:28 pm, Lennart Jonsson
> wrote:> On 2010-06-03 17:25, Jerry Stuckle wrote:
> > [...]
>
> > > It's quite common to have multiple clients with one company.  Different
> > > departments, for instance, may have separate accounts.  But billing
> > > still goes to the same place.
>
> > > And each account may have one or more users authorized to use the
> > > account; each user would have to be inserted.
>
> > All agreed, but I don't think I would end up with a data model like the
> > OP's, no matter what
>
> An author who likes playing with language (e.g. Terry Pratchett or
> Douglas Adams) would say something like:
>
> "There is no such thing as a perfect data model and this is an
> excellent example."

I can see everyone who has comments suggests that this is an imperfect
model. I would welcome any feedback on why its imperfect, and what
everyone else would do instead.

Regards
Dave.
 >> Stay informed about: Multiple Inserts across multiple tables 

> We have a number of tables in our database, that when a new account is
> created it should insert into all of them (different information) get
> the insert ids from all and the update the first table with all the
> ids (probably not the best way to do it, so if anyone can point me in
> the right direction, feel free).
>
> What I want to know is, if one of the inserts failed, is it possible
> to rollback all the other inserts. I have read a few articles and
> believe its possible if using InnoDB, but not sure about MyISAM.

Well, you should off course try it, but an SQL script like this would
demonstrate how to "consistently fail":

BEGIN;
SET @AccountId=NULL;
INSERT INTO client_account(...) VALUES(...); -- (supply your values)
SET @AccountId=LAST_INSERT_ID();
INSERT INTO client_company_name(acc_id,companyname)
VALUES(@AccountId, 'YourNameHere');
INSERT INTO client_address(acc_id, ...) VALUES(@AccountId, ...);
COMMIT;

If you feed such a script into the MySQL command-line client, it would
start a transaction (yes, you need a transactional engine like InnoDB
for this to work), and do the inserts. If one of them fails, the client
disconnects and the transaction is rolled back.

By the way, there are a lot of ways that inserts can "fail" without it
being a failure from a human point of view. To deal with existing data,
for instance, the INSERT .. ON DUPLICATE KEY syntax can be used.

Best regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/
 >> Stay informed about: Multiple Inserts across multiple tables 

On Jun 4, 10:37 am, David wrote:
> On 4 June, 09:27, Captain Paralytic wrote:
>
>
>
>
>
> > On Jun 3, 7:28 pm, Lennart Jonsson
> > wrote:> On 2010-06-03 17:25, Jerry Stuckle wrote:
> > > [...]
>
> > > > It's quite common to have multiple clients with one company.  Different
> > > > departments, for instance, may have separate accounts.  But billing
> > > > still goes to the same place.
>
> > > > And each account may have one or more users authorized to use the
> > > > account; each user would have to be inserted.
>
> > > All agreed, but I don't think I would end up with a data model like the
> > > OP's, no matter what
>
> > An author who likes playing with language (e.g. Terry Pratchett or
> > Douglas Adams) would say something like:
>
> > "There is no such thing as a perfect data model and this is an
> > excellent example."
>
> I can see everyone who has comments suggests that this is an imperfect
> model. I would welcome any feedback on why its imperfect, and what
> everyone else would do instead.
I did that in my first post in this thread.
If you need a 1:m relationship, you do it using the primary key from
the main (1) table and using it as a foreign key in any (m) tables.
If you have a 1:1 relationship, then you either put all the data in a
single table or, if you have a good reason for spreading the data
amongst many tables, you use the same primary key value in all of
them.

Really all you have to do is to follow the standard normalisation
rules:
http://delicious.com/Captain_Paralytic/normalization
 >> Stay informed about: Multiple Inserts across multiple tables 

On 4 June, 12:50, Captain Paralytic wrote:
> On Jun 4, 10:37 am, David wrote:
>
> > On 4 June, 09:27, Captain Paralytic wrote:
>
> > > On Jun 3, 7:28 pm, Lennart Jonsson
> > > wrote:> On 2010-06-03 17:25, Jerry Stuckle wrote:
> > > > [...]
>
> > > > > It's quite common to have multiple clients with one company.  Different
> > > > > departments, for instance, may have separate accounts.  But billing
> > > > > still goes to the same place.
>
> > > > > And each account may have one or more users authorized to use the
> > > > > account; each user would have to be inserted.
>
> > > > All agreed, but I don't think I would end up with a data model like the
> > > > OP's, no matter what
>
> > > An author who likes playing with language (e.g. Terry Pratchett or
> > > Douglas Adams) would say something like:
>
> > > "There is no such thing as a perfect data model and this is an
> > > excellent example."
>
> > I can see everyone who has comments suggests that this is an imperfect
> > model. I would welcome any feedback on why its imperfect, and what
> > everyone else would do instead.
>
> I did that in my first post in this thread.
> If you need a 1:m relationship, you do it using the primary key from
> the main (1) table and using it as a foreign key in any (m) tables.
> If you have a 1:1 relationship, then you either put all the data in a
> single table or, if you have a good reason for spreading the data
> amongst many tables, you use the same primary key value in all of
> them.
>
> Really all you have to do is to follow the standard normalisation
> rules:http://delicious.com/Captain_Paralytic/normalization

Ok, Now I am really confused, because i thought that is what i am
doing?

insert an address, get the last_insert_id and then update the account
table with that id.
I do the same for the contacts, email addresses and numbers

So if im being a bit thick forgive me, but isnt that what you are
talking about?
 >> Stay informed about: Multiple Inserts across multiple tables 

On 4 June, 13:13, Jerry Stuckle wrote:
> David wrote:
> > On 4 June, 12:50, Captain Paralytic wrote:
> >> On Jun 4, 10:37 am, David wrote:
>
> >>> On 4 June, 09:27, Captain Paralytic wrote:
> >>>> On Jun 3, 7:28 pm, Lennart Jonsson
> >>>> wrote:> On 2010-06-03 17:25, Jerry Stuckle wrote:
> >>>>> [...]
> >>>>>> It's quite common to have multiple clients with one company.  Different
> >>>>>> departments, for instance, may have separate accounts.  But billing
> >>>>>> still goes to the same place.
> >>>>>> And each account may have one or more users authorized to use the
> >>>>>> account; each user would have to be inserted.
> >>>>> All agreed, but I don't think I would end up with a data model like the
> >>>>> OP's, no matter what
> >>>> An author who likes playing with language (e.g. Terry Pratchett or
> >>>> Douglas Adams) would say something like:
> >>>> "There is no such thing as a perfect data model and this is an
> >>>> excellent example."
> >>> I can see everyone who has comments suggests that this is an imperfect
> >>> model. I would welcome any feedback on why its imperfect, and what
> >>> everyone else would do instead.
> >> I did that in my first post in this thread.
> >> If you need a 1:m relationship, you do it using the primary key from
> >> the main (1) table and using it as a foreign key in any (m) tables.
> >> If you have a 1:1 relationship, then you either put all the data in a
> >> single table or, if you have a good reason for spreading the data
> >> amongst many tables, you use the same primary key value in all of
> >> them.
>
> >> Really all you have to do is to follow the standard normalisation
> >> rules:http://delicious.com/Captain_Paralytic/normalization
>
> > Ok, Now I am really confused, because i thought that is what i am
> > doing?
>
> > insert an address, get the last_insert_id and then update the account
> > table with that id.
> > I do the same for the contacts, email addresses and numbers
>
> > So if im being a bit thick forgive me, but isnt that what you are
> > talking about?
>
> You have a 1:m relationship (client:address).  How do you indicate
> multiple addresses if you are inserting the address id in the client table?
>
> Rather, client_id should be a member of address, and nothing need be
> updated in client.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck....TakeThisOut@attglobal.net
> ==================

Oh, so im doing it the wrong way round.
 >> Stay informed about: Multiple Inserts across multiple tables 

On 4 June, 14:05, Jerry Stuckle wrote:
> David wrote:
> > On 4 June, 13:13, Jerry Stuckle wrote:
> >> David wrote:
> >>> On 4 June, 12:50, Captain Paralytic wrote:
> >>>> On Jun 4, 10:37 am, David wrote:
> >>>>> On 4 June, 09:27, Captain Paralytic wrote:
> >>>>>> On Jun 3, 7:28 pm, Lennart Jonsson
> >>>>>> wrote:> On 2010-06-03 17:25, Jerry Stuckle wrote:
> >>>>>>> [...]
> >>>>>>>> It's quite common to have multiple clients with one company.  Different
> >>>>>>>> departments, for instance, may have separate accounts.  But billing
> >>>>>>>> still goes to the same place.
> >>>>>>>> And each account may have one or more users authorized to use the
> >>>>>>>> account; each user would have to be inserted.
> >>>>>>> All agreed, but I don't think I would end up with a data model like the
> >>>>>>> OP's, no matter what
> >>>>>> An author who likes playing with language (e.g. Terry Pratchett or
> >>>>>> Douglas Adams) would say something like:
> >>>>>> "There is no such thing as a perfect data model and this is an
> >>>>>> excellent example."
> >>>>> I can see everyone who has comments suggests that this is an imperfect
> >>>>> model. I would welcome any feedback on why its imperfect, and what
> >>>>> everyone else would do instead.
> >>>> I did that in my first post in this thread.
> >>>> If you need a 1:m relationship, you do it using the primary key from
> >>>> the main (1) table and using it as a foreign key in any (m) tables.
> >>>> If you have a 1:1 relationship, then you either put all the data in a
> >>>> single table or, if you have a good reason for spreading the data
> >>>> amongst many tables, you use the same primary key value in all of
> >>>> them.
> >>>> Really all you have to do is to follow the standard normalisation
> >>>> rules:http://delicious.com/Captain_Paralytic/normalization
> >>> Ok, Now I am really confused, because i thought that is what i am
> >>> doing?
> >>> insert an address, get the last_insert_id and then update the account
> >>> table with that id.
> >>> I do the same for the contacts, email addresses and numbers
> >>> So if im being a bit thick forgive me, but isnt that what you are
> >>> talking about?
> >> You have a 1:m relationship (client:address).  How do you indicate
> >> multiple addresses if you are inserting the address id in the client table?
>
> >> Rather, client_id should be a member of address, and nothing need be
> >> updated in client.
>
> > Oh, so im doing it the wrong way round.
>
> Yes.  You way, how would you specify multiple addresses for a single
> company?
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck....TakeThisOut@attglobal.net
> ==================

The account_id is saved in the address table along with the type of
address (ie billing, registration, local office etc..)
 >> Stay informed about: Multiple Inserts across multiple tables 

On 4 June, 14:10, Jerry Stuckle wrote:
> David wrote:
> > On 4 June, 14:05, Jerry Stuckle wrote:
> >> David wrote:
> >>> On 4 June, 13:13, Jerry Stuckle wrote:
> >>>> David wrote:
> >>>>> On 4 June, 12:50, Captain Paralytic wrote:
> >>>>>> On Jun 4, 10:37 am, David wrote:
> >>>>>>> On 4 June, 09:27, Captain Paralytic wrote:
> >>>>>>>> On Jun 3, 7:28 pm, Lennart Jonsson
> >>>>>>>> wrote:> On 2010-06-03 17:25, Jerry Stuckle wrote:
> >>>>>>>>> [...]
> >>>>>>>>>> It's quite common to have multiple clients with one company.  Different
> >>>>>>>>>> departments, for instance, may have separate accounts.  But billing
> >>>>>>>>>> still goes to the same place.
> >>>>>>>>>> And each account may have one or more users authorized to use the
> >>>>>>>>>> account; each user would have to be inserted.
> >>>>>>>>> All agreed, but I don't think I would end up with a data model like the
> >>>>>>>>> OP's, no matter what
> >>>>>>>> An author who likes playing with language (e.g. Terry Pratchett or
> >>>>>>>> Douglas Adams) would say something like:
> >>>>>>>> "There is no such thing as a perfect data model and this is an
> >>>>>>>> excellent example."
> >>>>>>> I can see everyone who has comments suggests that this is an imperfect
> >>>>>>> model. I would welcome any feedback on why its imperfect, and what
> >>>>>>> everyone else would do instead.
> >>>>>> I did that in my first post in this thread.
> >>>>>> If you need a 1:m relationship, you do it using the primary key from
> >>>>>> the main (1) table and using it as a foreign key in any (m) tables..
> >>>>>> If you have a 1:1 relationship, then you either put all the data in a
> >>>>>> single table or, if you have a good reason for spreading the data
> >>>>>> amongst many tables, you use the same primary key value in all of
> >>>>>> them.
> >>>>>> Really all you have to do is to follow the standard normalisation
> >>>>>> rules:http://delicious.com/Captain_Paralytic/normalization
> >>>>> Ok, Now I am really confused, because i thought that is what i am
> >>>>> doing?
> >>>>> insert an address, get the last_insert_id and then update the account
> >>>>> table with that id.
> >>>>> I do the same for the contacts, email addresses and numbers
> >>>>> So if im being a bit thick forgive me, but isnt that what you are
> >>>>> talking about?
> >>>> You have a 1:m relationship (client:address).  How do you indicate
> >>>> multiple addresses if you are inserting the address id in the client table?
> >>>> Rather, client_id should be a member of address, and nothing need be
> >>>> updated in client.
> >>> Oh, so im doing it the wrong way round.
> >> Yes.  You way, how would you specify multiple addresses for a single
> >> company?
>
> > The account_id is saved in the address table along with the type of
> > address (ie billing, registration, local office etc..)
>
> Ok, so then why do you need to update in the account table?
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck....RemoveThis@attglobal.net
> ==================

Yes, I agree with you now. I see that its duplication and totally not
needed !
 >> Stay informed about: Multiple Inserts across multiple tables 

> I can see everyone who has comments suggests that this is an imperfect
> model. I would welcome any feedback on why its imperfect, and what
> everyone else would do instead.

Links between tables only need to go one side. So if you have, for
instance, a general Address table, you can insert an address, get its
autonumber value and store that in the accounts table.

It helps to define a foreign key relation in the table. That way, there
are no records that point to related records that no longer exist.

Best regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/
 >> Stay informed about: Multiple Inserts across multiple tables