Multiple Inserts across multiple tables

I would insert into 1 table and then use the ID from that one as the
primary key to use on inserting to all the rest.

And yes, if you want Commit/Syncpoint/Rollback functionality you need
to use InnoDB.

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.


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

[...]

<snip unrelated info>


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.
 XXXX@XXXXX.COM 
==================

[...]

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

[...]

On Jun 3, 7:28m, Lennart Jonsson < XXXX@XXXXX.COM >




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.

> I can see everyone who has comments suggests that this is an imperfect

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.**--****.com/ 

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://www.**--****.com/ 

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.
 XXXX@XXXXX.COM 
==================

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.
 XXXX@XXXXX.COM 
==================