how can i insert a record into a table that has links to other table through foreign keys

You might want to create a stored-procedure to:
1. Check the publisher, if exists get the publisherid, else add new publisher and return the publisherid
2. Check the author, if exists get the authorid, else add new author and return the authorid
3. Insert new record to book table with publisherid and authorid you get from 2 steps above

But note that you might have to pass not only fields of book, but also fields of publisher (date_published, name) and author (fname, surname).

Hi Sudani, welcome to EE.

I will give you my opinion in your DB design, not necessarily the best but, is correct from where I see your structure.
First: You do not need to normalize Author_Book and Book_Category.
On the other hand, Book Table should add these extra fields "authorID", "date_published", "categoryID".

Secondly, The Input process:
Normally, in the front application (the interface) you would be inserting the Book with input fields. But Author, Publisher and Category with select only (drop-down list). And while inserting a book where Author, Publisher and/or Category not listed you add them separately, before proceeding with the book insert operation with reference only to AuthorID, PublisherID and CategoryID.

But you could do it the approach you are referring to, where you have many input fields for books as well as for Author, Publisher and Category, but you risk to double entry Author, Publisher or Category in case you misspelled any of them from existing ones (human error).

@Duy Pham..
So my pseudo code should be like:

Insert into author (Cl1,cl2,...) values (val1,val2,..)
--check if exist authorId 
 If exists select authorId from  author where authorid = @last_insertedId

Select allOpen in new window

Could you advice if this this is the right track as sql query  structure concern

@Duy Pham..
this what i wanted to do..
thanks for guidance

the expert has been patient as he respond to my comments and figured out what i want only from my plain comments and transfered the comments to practical working solution

Hi guys
sorry to come back to the topic as I'm facing uphill struggle on the final insert into book table i have created the following statement followed @Duy Pham instruction as foolw

DELIMITER $$

USE `mybook_store`$$

DROP PROCEDURE IF EXISTS `myProc4`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `myProc4`()
BEGIN
	DECLARE authId INT DEFAULT 0;
	DECLARE pubId INT DEFAULT 0;
	DECLARE categId INT DEFAULT 0;
	DECLARE CheckExists INT DEFAULT 0;  
	SET CheckExists = 0;
	BEGIN
	SELECT COUNT(*) INTO CheckExists FROM author WHERE fname = 'xxxx' AND surname='xxxx'; 
		IF (CheckExists > 0) THEN
		SELECT  authorid INTO authId FROM author WHERE fname = 'xxxx' AND surname='xxxx';
			ELSE 
    
			INSERT author(fname, surname) VALUES('xxxx', 'xxxx');
			
			SET authId = LAST_INSERT_ID();
			INSERT author_book (authorId) VALUES(authId);
			SELECT authId;
		END IF;
          
          END;
          BEGIN
          SELECT COUNT(*) INTO CheckExists FROM publisher WHERE publisher.Name = 'xxxx' ; 
		IF (CheckExists > 0) THEN
		SELECT  publisherId INTO pubId FROM publisher WHERE publisher.Name = 'xxxx';
			ELSE 
    
			INSERT publisher(publisher.Name) VALUES('xxxx');
			SET pubId = LAST_INSERT_ID();
			SELECT pubId;
		END IF;
          END;
          BEGIN
          SELECT COUNT(*) INTO CheckExists FROM category WHERE category.Name = 'test1' ; 
		IF (CheckExists > 0) THEN
		SELECT  categoryId INTO categId FROM category WHERE publisher.Name = 'test1';
			ELSE 
    
			INSERT category(category.Name) VALUES('test1');
			SET categId = LAST_INSERT_ID();
			SELECT categId;
		END IF;
          END;
    END$$

DELIMITER ;

Select allOpen in new window

i have bridge tables such as author_book and book_category and they are linked to the book table:

1.

my question is how do i get about inserting the value into the book table and passing the value to bridge tables.

2.

how to go about inserting the value of existing item e.g. if author exists how to go about inserting it to the book table
thanks

@Duy Pham...
i have followed your instruction and thanks to you my query up and running and performing what it is supposed to do...

You're welcome :).

Happy coding.