On 20 Jun 2011, at 4:04am, Michael Parker wrote:
> Newbie question here: Imagine I’m writing a chat server with multiple
> chat rooms, where each chat room has its own unique identifier. I want
> to store the messages from each chat room and allow later retrieval of
> all messages in a chat room in their proper order. I was thinking how
> to do this with one table (this is an iPhone app so I’m being a little
> resource-conscious), and was thinking of something like:
>
> CREATE TABLE Messages (chat_room_id TEXT NOT NULL, message_id INTEGER
> NOT NULL, message TEXT NOT NULL, PRIMARY KEY (plan_id, message_id));
>
> What I’d like is to be able to do something like this:
>
> INSERT INTO Messages (chat_room_id, message) VALUES ('chat_room_id1',
> 'message1');
First, let the system pick unique integers for chat_room_id. It's faster
searching for integers than text. You can create another table for Chatrooms
which has columns for the integer id, short name, and long name of each chat
room.
Then let the system pick unique integers for message_ids too. Every message
gets its own ID and they're all in the same sequence, no matter what chat room
the message is in. Use that as your primary key. In your message table, have
a column for the chat room (the integer). You can make a secondary key for the
Messages TABLE which has (chatroom_id, message_id) and that will let you find
all messages for a certain chat room.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
