SQLite creating Tables

What you should do is create a relational map. It's essentially a drawing of what the schema or structure will look like. What you will need is to utilize is Primary keys and Foreign Keys. This will allow you to associate columns from one table, with data in a column from another table.

Now, the tricky part comes in when trying to save the date of each message. You can't group messages all into a single expanding field in a row. Because there would be no way to associate the date. You also have to think about what you might not have thought about. Things like, will I need to add a block user feature? How am I going to Delete messages without deleting the users? Say I like usersNames with Contacts? If I remove a message I don't want to remove the user for it could effect my Contacts list. That sort of thing.

As I am writing as fast as I can think. I can tell this will be a long one so stay with me here lol.

I would do this!

Table 1
ID, UserName

Table 2
UserName, Message, Date

I would make a primary key with ID in Table 1 because it should never be null. I would link UserName from Table 1 with UserName in Table 2 with a foreign key.

If I text you for the first time. It creates a new Row in table 1 with an ID that is auto-incremented. So if I were the first person to text you, I would have an ID of 1. Then my username Ryan.

Table 1
ID,  Username
1    | Ryan

My text message date and anything else I wanted would actually go in table 2 but be linked together by UserName

Table 2
UserName,  Message,   Date
Ryan       Hello       9/9/2009

Now, if I text you again. I would ask, does this userName exist in the UserName Column of table 1. YES! Ok cool, let's grab that UserName and create a new Row in Table 2 with the UserName Message and Date etc. So each new message is stored in table 2 as a new row. This will allow you to save the date of each individual message.

Table 1
ID,  Username
1    | Ryan


Table 2
UserName,  Message,  ---   Date
Ryan       Hello           9/9/2009
Ryan       Whats up       9/9/2009

This does mean that Table 2 will have several rows with the same UserName. You might want to save the date by day and seconds. That way if two messages come in close together, you can still query them seperately. You may get a lot of messages in a single day from the same user. You may or may not want them associated with such a broad date. Not sure if it matters or not.

If you do do date, you may want a third table called date. Removing Date from Table 2 and replacing it with say an ID_2. That way you can match up ID_2 from Table 2 with a new Table 3 called Date. You can use the ID_2 and some column in the date table. So that if multiple messages come in from the same user in the same day. You only save 1 date for that user. And all their messages are associated with a single date. This would use less memory.

Than your tables will look like this after a bunch of people text you.

Table 1
ID,  Username
1    | Ryan
2   | Sally
3   | Hottie
4   | Crazy 



Table 2
UserName, Message,  ---   Date
Ryan         Hello         9/9/2009
Ryan         Whats up      9/9/2009
Sally        Yo            9/10/2009
Crazy        WOAH!        9/11/2009
Hottie       Hey Babe     9/11/2009
Crazy        WTF!         9/12/2009
Sally        Dinner?     9/13/2009

Does this make sense? This way you can query the number of unique users you have. With Table 1. Than you can query all the messages where userName in Table 2 equals (either Table 1 UserName or you can give a userName).

ALSO when you delete messages. You can delete them from Table 2. No need to touch Table 1. Table two will find the Message and delete it.

Say you deleted all the messages but mine.

Table 1
ID,  Username
1   | Ryan
2   | Sally
3   | Hottie
4   | Crazy 



Table 2
UserName, Message,  ---   Date
Ryan       Hello          9/9/2009
Ryan       Whats up       9/9/2009

Now let's say Crazy is getting out of hand. What if I want to block them, but I don't want to delete their contact data that I associated with it at some point. I can add a column to Table 1 called BLOCK. Which wont save the texts, userName or anything in Table 2.

Table 1
ID,  Username    BLOCK
1    | Ryan           N
2   | Sally           N
3   | Hottie          N
4   | Crazy           Y

Does this make sense at all? I kinda just started typing and 15 minutes later this is what I have lol.. Let me know. Thanks!