sudani1969
asked on
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 :
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
my first post :)
i have schema as follow :
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
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).
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).
ASKER
@Duy Pham..
So my pseudo code should be like:
Could you advice if this this is the right track as sql query structure concern
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
Could you advice if this this is the right track as sql query structure concern
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Duy Pham..
this what i wanted to do..
thanks for guidance
this what i wanted to do..
thanks for guidance
ASKER
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
ASKER
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
i have bridge tables such as author_book and book_category and they are linked to the book table:
thanks
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 ;
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 tablethanks
You should insert book value right after you get the categId (after line 49 from above code in your comment: https://www.experts-exchange.com/questions/28641131/how-can-i-insert-a-record-into-a-table-that-has-links-to-other-table-through-foreign-keys.html?anchorAnswerId=40705043#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?
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)
INSERT book_category(ISBN13, categoryId) VALUES(bookid_as_ISBN13, categId)
ASKER
@Duy Pham...
i have followed your instruction and thanks to you my query up and running and performing what it is supposed to do...
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.
Happy coding.
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).