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

Hi  
my first post :)
i have schema as follow :
this how are tables are linked
my question is how to go about inserting a new book record and at the same time check if an author exists insert his/her details if not not insert new one the same goes to publisher and category(one book could belong to many category any direction or example will be great
sudani1969Asked:
Who is Participating?
 
Duy PhamFreelance IT ConsultantCommented:
Could be something like this:

IF EXISTS (SELECT * FROM [Author] WHERE <Conditions_to_validate_unique_Author>)
    SELECT @AuthorId = authorid FROM [Author] WHERE <Conditions_to_validate_unique_Author>
ELSE
    BEGIN
          INSERT [Author](Cl1, Cl2, ...) VALUES(val1, val2, ...)
          SET @AuthorId = <Last_Inserted_Author_Id>  -- Could be @@IDENTITY if authorid is IDENTITY column
    END

....
-- Do the same for Publisher...

INSERT [Book](authorid, publisherid, BookField1, BookField2,...) VALUES(@AuthorId, @PublisherId, bookvalue1, bookvalue2, ...)

Open in new window


Hope this helps.
0
 
Duy PhamFreelance IT ConsultantCommented:
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).
0
 
jimyXCommented:
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).
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
sudani1969Author Commented:
@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

Open in new window


Could you advice if this this is the right track as sql query  structure concern
0
 
sudani1969Author Commented:
@Duy Pham..
this what i wanted to do..
thanks for guidance
0
 
sudani1969Author Commented:
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
0
 
sudani1969Author Commented:
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 ;

Open 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
0
 
Duy PhamFreelance IT ConsultantCommented:
You should insert book value right after you get the categId (after line 49 from above code in your comment: http://www.experts-exchange.com/Database/MySQL/Q_28641131.html#a40705043)

Code in line 23 in your procedure above is too early
INSERT author_book (authorId) VALUES(authId);You need to insert book first to keep integrity in case error happens when adding book value. Also I assume that the code should be something like:
INSERT author_book (authorId, ISBN13) VALUES(authId, bookid_as_ISBN13);Is this answer your first question about adding value to author_book table?

Finally just add record for book_category table
INSERT book_category(ISBN13, categoryId) VALUES(bookid_as_ISBN13, categId)
0
 
sudani1969Author Commented:
@Duy Pham...
i have followed your instruction and thanks to you my query up and running and performing what it is supposed to do...
0
 
Duy PhamFreelance IT ConsultantCommented:
You're welcome :).

Happy coding.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.