Hi!

Suppose I have a many-to-many relationship. E.g:

Students (ID, name)
Student-Class (StudentID, ClassID)
Classes (ID, class)

Now, if I want to get information about all students and what classes they have, no matter if the numer of classes is 0 I could use


(Is there a smarter way by the way?)

But how do I make an insert?

Let's say I want to add Adam to a class of Astronomy.

First I have to check if Adam exists in Students, if not add him there.
Then I have to check if Astronomy exists in classes, if not add it there.
By now I know Adam is in Students and Astronomy is in Classes.
Now I have to check if the Students.ID for Adam exists in combination with the Classes.ID for Astronomy in the table student-class, if not add it there.

Ok, how do I do this?
In one statement or in two, or three or...
If in three (which I suspect) how do I e.g. get the Students.ID for Adam if he alreay existed? Same question if he didn't exist prior to insert?

I'd very much like to solve this with SQL and VB and maybe stored procedures but not with user functions or LINQ (since I'm not sure I am finally going to use SQL2005 for my DB server).

Thanks
Jens

This post has been edited by jens: 13 Oct, 2008 - 12:55 PM

In general, SQL doesn't allow you to insert into mutiple tables with a single statement. So yes, you're going to have to do a separate insert into each table.

Ok, but how do I go about that?

Could I write a stored procedure that accepts a user name and return its ID wether or not it already existed?

In pseudo code I'd like somthing like...


Is this possible in SQL and stored procedures? In any other way in case the answer is no. (If answer is yes, please tell me how)

/Jens

This post has been edited by jens: 14 Oct, 2008 - 12:15 PM

What do you mean, "how do I go about that?" You figure out what values you need in each table and run the 3 corresponding INSERT statements. I don't understand what part of this you're having a problem with.


Yeah, you could do that if really you wanted to. You could also do it in your application code. Either way will work. All you need to do is run a SELECT to determine the ID of the student in question, and if no rows are returned, INSERT a record for that student. If you're using an auto-numbered ID field, then you'll also have to return the generated ID (the exact method for getting that is dependent on your database). That's it. It's a tedious, multi-step operation, but it's not really that complicated.

There, that's my problem.

Imagine you are going to do this from a browser with very limited bandwidth and several concurrent users working on the database (probably MS SQL server but maybe MySQL). This gives a lot of things to think about with locking tables and efficiency and such.

Of course I can use IF EXISTS and test for the user, then for the course then for the combination of user and course but as you point out; it is tedious and I guess not very efficient.

The example with students - courses is given to get my point across. The actual project I'm trying to work on uses lots and lots of many-to-many realtions so I'd like to handle them as efficiently as possible.

I think that leaves the question unanswered: How to do this (many-to-many insert) efficiently in a multi-user enviroment with low bandwith between user and server. Or, is your answer the only way to go?

/Jens

Ah, now things are coming together.

Most of the concerns you've mentioned don't typically apply to web-based apps, so I'm guessing you're working on a connected desktop application, correct? That changes things - especially if you have low bandwidth.

If you want to wrap a multi-table insert up into a single database call, you pretty much have to write a stored procedure. I don't think there's any other way to do it. On the up side, that will cut down on your network traffic and take some processing load off the client. On the down side, some of the procedures could get kind of complicated and if you decide to switch database servers it will be a pain to port them.

As far as efficiency goes, that's always going to be relative to your hardware and server load. I obviously don't know what kind of servers you have or how many concurrent users you're looking at, but I would encourage you to do some testing before jumping to conclusions about what will and won't be efficient. While there are a lot of operations involved in a many-to-many insert, none of them should be particularly involved - just simple selects and inserts. Assuming your tables are properly indexed and the server is running smoothly, each individual operation should be pretty fast.

Uh, ohhh... Well, thing is I do have some knowlede about databases but hav mainly done my work in an other language than SQL and I've been working with connected apps in massively concurrent enviroments. (Like 500 concurrent users and several batches and other processes working on the DB simultaneously)

Now I've set out to make me a website that will (if I get there and it is a sucsess) have several hunddreds of users at the same time doing reads, updates and inserts on 50+ many-to-many-to-many relations.

You tell me things work differently with web - one more thing to dig in to. Would you care to elaborate a little?

There must be deadlocks in a web-connected DB, no?
There must be problems with transaction handling too? (I.e. should you use optimistic transaction handling? What to do when rolling back due to record change while editing? And so on)

I must admit I haven't gripped what's so special with web stuff. Isn't it just Like a low bandwidth connected app that opens and closes connections to the DB all the time?

Regards
Jens

Well...yes and no.

As far as low-bandwidth goes, it's not the same. With connected apps, a low-bandwidth connection to the DB can kill performance. I'm sure you're familiar with that.

With web apps, unless you have a seriously brain-damaged network architecture, you should never have a low-bandwidth DB connection, since it's all contained within your back-end network. So your bottleneck on database operations should be query performance, not connection speed. You may still have a low-bandwitdh connection to the client, but that doesn't really have anything to do with your database queries. By the time the client gets back the page he requested, the DB work has long since been completed.

As for locking, it depends on what you mean. Locking records within a transaction is still the same. But that's not a problem in web apps because most of your transactions will finish quite quickly.

If you're talking about placing write locks on records that are currently being edited, that's a different story. You can't really do that in a web app because your edit is spread out across multiple connections, let alone transactions. It may be possible to lock records across connections, but I've never tried it and the general concensus seems to be that it's best avoided.

Of course, you can always do your locking on the application level (e.g. have your own lock table or something like that) to achieve the same effect. For instance, look at this DDJ article on the subject. It's a different sort of approach, though.

Hope that clears up what I was saying a little bit.