[pertheusual]

Hey All,
So, some background info.
I'm making a DB to manage messages and associate those messages with one or more groups.
I have a table of messages and a table of groups with each group and message each having a unique ID(auto_increment).
I also have a combo table with a column for messageID and a groupID to associate them with eachother.
Then to get messages for a specific group, I can just do a simply join based on the combo group.

So, two question.

First, I'm still pretty new to databases. Is this a good way to organize the tables?

Second, when inserting a message, I'll then have to know what the id is for the combo table. If I use auto increment, how do I get it? I know I can do a normal Select with the statements I just inserted, but that seems really inefficient. Is there any way to immediately select the last row inserted or anything?

That also seems bad because, what if I had 2 messages with the same info? Would I just select with the inserted values, then check the found rows for the highest messageId?

I hope I explained that ok. I may have answered all my questions myself, but figured I might as well ask.

Thanks much.

Per

[Martyr2]

The answer to your first question, yes that is exactly how you should have it setup. The "combo table" is actually called a conjunction table and it is designed exactly for the purpose you have there. This conjunction table allows you to have the flexibility of a one to many relationship or a many to many relationship (instead of a one to one you would have had if you just linked the two tables). With that setup you can now have one group which can have multiple messages.

As for your second question, you can insert your message into the message table and (depending on the database) query for the last ID created or like you mentioned do a query for the highest ID and fetch that. So it would work like this...

1) Assuming you have the group id already before you make the message for that group, insert the message into the messages table.

2) Run a query to then find the highest ID (or in the case of something like SQL Server you can use Scope_identity etc like documented here... Alternatives to @@IDENTITY)

3) Insert a record using the group id and the message id into the conjunction table to finish the linking up.

Typically you would do this setup in some form of transaction mechanism so that if one of the inserts or queries fail, the whole thing can be rolled back.

But yeah, good setup you have there, keep following through with it.

This post has been edited by Martyr2: 28 February 2008 - 05:41 PM

[pertheusual]

I'm using MYSQL, I found the function "LAST_INSERT_ID()", which is just what I was looking for.

Thanks much for confirming that it's possible Marty.
Looking for something without know if it is can be done is always annoying.

Per