Forum Moderators: open
This is my first time building a relational database. I have set it up like this:
Two tables: tickets and messages
A single ticket can have many messages
The tickets table has the following fields:
ticketid (Primary)
ticketkey (a randomly generated password to access the ticket)
name
date
email
The messages table has the following fields:
msgid (Primary)
ticketid (Foreign Key)
name
date
msgcontent
My problem is this: how do I get information into both tables at the same time? I have a couple of MySQL books and it just isn't sinking in. I have read and reread the sections on JOIN and it doesn't make sense to me. A new ticket should be created every time a message is submitted that doesn't already belong to a ticket. So I need to somehow create an INSERT that creates the ticket (got that part already), and then creates a message with the same ticketid?
So I entered some dummy data by hand. Second question, how do I get the data back out? I need to do a select that pulls all of the messages back out associated with a particular ticketid, based on the secret password mentioned in the tickets table. So, each unique ticket has a password, that password grants access to all messages with that ticketid.
Sorry if this is exceptionally simple. I am very new to database programming and was happy to even figure out that I needed two different tables and (I think) how to link them. If anyone can give some advice (or a better book or site to work from) that would be great.
Thanks,
Dalton
INSERT INTO tickets(ticketid,ticketkey, name, date, email)VALUES(NULL,'key', 'name', 'date', 'email');
INSERT INTO messages(msgid, ticketid, name, date, msgcontent) VALUES(NULL, LAST_INSERT_ID(),'name', 'date', 'content');and to select all the messages for a ticket you can do something like.
SELECT message.* FROM messages, tickers WHERE tickets.ticketid = messages.messageid AND tickets.ticketid = 'ticketid' AND tickets.ticketkey = 'ticketkey';make sense?
[edited by: Zipper at 7:57 am (utc) on May 23, 2008]
So I guess the deal with relational databases is that the relationships must be maintained in the way you enter the data and are not automatically enforced by the database itself? I had always assumed (being a complete database neophyte) that creating a new ticket and associated message with a shared field (in this case the ticketid field is the foreign key in the messages table), the database would somehow magically link these two records together permanently, but in fact it would be pretty easy to attach a message to the wrong ticketID or change them after the fact and wreak havoc on the whole system.
This is very good to know. But your advice worked and completely answered my question, thank you.
So I guess the deal with relational databases is that the relationships must be maintained in the way you enter the data and are not automatically enforced by the database itself?
Yes and no, depending on your database engine and the way you created the database. Transactional databases allow you to add "maintenance" functions to tables (files). Outside of that, you maintain the data integrity through your programming logic. If you search for the term "database triggers" you can learn a bit more about advanced database functions.
...WHERE tickets.ticketid = messages.messageid AND...
As your queries get more complex, you're going to want to understand joins. The one above is fine for one table, but if you join on two or three tables you'll want to use join. A synonymous query to the example provided:
select message.* from messages where tickets.ticketid = 'ticketid' and tickets.ticketkey = 'ticketkey' left join tickets on tickets.ticketid = messages.messageid;
