Need help with Many to Many DB and Inserting.

Question

Hi, I'm trying to do an Insert into a couple tables on one form with data controls. The problem is I don't know the approach. You see, the client has books to sell. He has catagories and he wants to add many catagories for each book. One or more catagories. It would seem to me that this is a many to many normalization. Being that one or more catagories can belong to one or more books? If this is incorrect, please tell me, as I thought it was a one to many earlier with one book having one or more catagories? Anyway, I need to be able to allow the client to enter one book, and then assign one or more catagories? He wants the potential buyers to be able to query books by catagories obviously. Wondering what approach to use? What data controls, and is it possible with just the controls, or do I need to write a stored procedure to grab the ID from the book using the @@Identity SQL statment? Thanks in advance, and any help would be most appreciated..

Thanks
Tim

Answer 1

Hoosier,

I know not any database for windows which is able to do a many to many relation. You always have an intermediate in between. 

Be aware the stored procedure is nothing more than a piece of SQL transact code which is stored on the Server Side. As soon as you start with an @@Identity you are back to the very basic of a database.

So first create a data base scheme where you have written the tables and with lines the relations, before you start any piece of code. 

A pencil and a piece of paper are your first friends if you don't know how to do that on a computer.

And if you have questions here, than at least tell us what brand of database you want to use and if there in that brand are more types the type.

Be aware that a many to many relation it is rare. It would only be if there would be in your sample of books two authors for one book and you want a related link to both of them. In those cases the co authorship will be probably placed in one item. 

---

Success
Cor

Answer 2

Dear Tim,

I completely agree with Cor on this. Start your project on paper, and draw yourself a good ERD. Many modern DBMS' support this with a graphical interface. Then, after you've designed your datamodel, move to visual studio and create the forms you need to work with it. If your datamodel was good, with the new versions of Visual Studio it is going to be a breeze to build your app.

What you've described in the post above, is what we call a "weak entity". A weak entity is a table used to record many to one relationships with more than one related table (* i know, read it twice *). Interesting characteristic of a weak entity, and therefore easily recognized, is that the table by itself, without the related tables, doesn't provide information -just data-.

In the above case i believe this is what you've described:

Notice how you can store multiple categories for each book using the Book_Category table. The table by itself does not provide -any- information, just numbers. A query that joins book, book_category and category together will return all books and all their related categories. Like so:

 

SELECT * FROM
	BOOK AS B
	INNER JOIN
	BOOK_CATEGORY AS BC
	ON B.BOOKID = BC.BOOKID
	INNER JOIN
	CATEGORY AS C
	ON BC.CATEGORYID = C.CATEGORYID

 

Looking for all books in a specific category, just extend the query with a conditional join parameter:

 

SELECT * FROM
	BOOK AS B
	INNER JOIN
	BOOK_CATEGORY AS BC
	ON B.BOOKID = BC.BOOKID
	INNER JOIN
	CATEGORY AS C
	ON BC.CATEGORYID = C.CATEGORYID AND C.CATEGORYID = 1

 

Add the above as a datasource in your project and design your forms:

Books and their categories		Categories and their books

 

Hope this helps,

Cheers,

John

 

 

Answer 3

Hello HoosierDaddyTim,

I am writing to check the status of the issue on your side. Would you mind letting us know the result of the suggestions? If you have any concerns, please feel free to follow up.

Have a nice day.

Best regards

---

Liliane Teng [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 4

John,

Thank you. I'm playing with this in Lightswitch now. I understand. I thought they were called Linking tables? Thank you so much.  I really appreciate it..

Tim

---

Tim

Answer 5

These were very helpeful. I apologize for not following up. I had some heart problems and have been offline for quite awhile. I am sorry I didn't see this lately. 
Regards. 

Tim

---

Tim