Inserting records into normalized db

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
LDusan
Forum Commoner
Posts: 45
Joined: Sun Mar 08, 2009 5:03 am
Location: Belgrade, Serbia

Inserting records into normalized db

Post by LDusan »

I posted question several months earlier, I researched a bit and decided to normalize my db.

So, I have, one table - books with all book relevant data, one authors table with author data, and one join table with book_id and author_id.

I was surprised I could not find the answer to a simple question - how do I add record into such, typical database. When a user fills the form with one book and authors, there should be one entry in books table, one or more entry in authors table and one or more entries in join table. How do I get IDs for the join table?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Inserting records into normalized db

Post by VladSun »

There are 10 types of people in this world, those who understand binary and those who don't
LDusan
Forum Commoner
Posts: 45
Joined: Sun Mar 08, 2009 5:03 am
Location: Belgrade, Serbia

Re: Inserting records into normalized db

Post by LDusan »

Exactly what I needed. Thanks a lot. I thought of:

Code: Select all

 
SELECT ID FROM BOOKS ORDER BY ID DESC LIMIT 1;
 
But why waste another query. Thanks.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Inserting records into normalized db

Post by VladSun »

LDusan wrote:Exactly what I needed. Thanks a lot. I thought of:

Code: Select all

 
SELECT ID FROM BOOKS ORDER BY ID DESC LIMIT 1;
 
But why waste another query. Thanks.
It's also not session safe - if someone inserts a record between the execution of this query and your insert query you'll get a wrong ID

http://dev.mysql.com/doc/refman/5.0/en/ ... ue-id.html
For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client. It is not even changed if you update another AUTO_INCREMENT column with a nonmagic value (that is, a value that is not NULL and not 0). Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously from multiple clients is perfectly valid. Each client will receive the last inserted ID for the last statement that client executed.
There are 10 types of people in this world, those who understand binary and those who don't
LDusan
Forum Commoner
Posts: 45
Joined: Sun Mar 08, 2009 5:03 am
Location: Belgrade, Serbia

Re: Inserting records into normalized db

Post by LDusan »

Great! Thanks a lot!
Post Reply