Storing a List in Database

Hi,
I wanted to have a table which has some messages stored with following columns:
MSG_ID MESSAGE POSTED_BY

I want to maintain a list of people who have read this message, who ever view the message will be submitting the confirmation that they have read it.
Now I want to link this table with may be a List which would tell me who have read the message.
Have some doubts how it can be done..
Right now i have two ideas :
Either i can create a table for each message which will have a column called MSG_READ_BY and will hold the names who have read it.
Second i was thinking i can store the name in a .txt file and give the save the path to the file in one of the columns of my message table.
but both seem to be quite complex to me..
I was thinking about saving may be an ArrayList in a column of the message table but actually dont know how to go about it.

would really appreciate any guidance.

-Thanks

Either i can create a table for each message which will have a column called MSG_READ_BY and will hold the names who have read it.

So something like:

where message_id is a foreign key to your message table's msg_id field? Sounds reasonable enough. What do you feel is complicated here? What are you unsure about?

Second i was thinking i can store the name in a .txt file and give the save the path to the file in one of the columns of my message table.

Sounds less reasonable. You are introducing a non-transational resource that is outside the database's control. So you introduce scope for data errors and weakening any security model your application uses. Plus a text file can only be amended by one user at a time, whereas a table in a database (like that above) can support many users performing operations concurrently.

I was thinking about saving may be an ArrayList in a column of the message table but actually dont know how to go about it.

You would be breaking a rule of first normal form here: that all values should be automic (i.e. 1 field == one value). Allowing multivalues introduces the need to parse the content of a field to understand it, demand the client not corrupt the data and introduces a bottleneck. I know Access supports multivalues columns, but if I were you I'd avoid using them.
[ March 04, 2008: Message edited by: Paul Sturrock ]

Thanks for the reply..
well do you mean creating a single table with lets say two coumns one for msg_id and other for let's say user_id indicating who has done the course?
something like this? :
Table_Read_By
MSG_ID USR_ID
1234 AB1234
8976 RT3452
1234 cc2345
1234 WE3425

am i getting you correctly?
but wouldnt it have do many rows for MSG_ID?
i mean i can have 100s of updates and any number of users..
or may be i am not getting you correctly

-Thanks for your time.

Yup you just described the classic many to many join table. And yes one MSG_ID would show up many times, once for each user who viewed it. And USER_ID would show up once for each message they viewed. This is a common pattern and makes it very easy to do things like - who viewed this message, what messages have I viewed, etc.

Originally posted by prakash chauhan:
Thanks for the reply..
well do you mean creating a single table with lets say two coumns one for msg_id and other for let's say user_id indicating who has done the course?
something like this? :
Table_Read_By
MSG_ID USR_ID
1234 AB1234
8976 RT3452
1234 cc2345
1234 WE3425

am i getting you correctly?

Yes. And as Tim points out, this is a common way of defining a many to many relationship in relational databases.

but wouldnt it have do many rows for MSG_ID?
i mean i can have 100s of updates and any number of users..
or may be i am not getting you correctly

If you have a lot of users and a lot of messages, you are likely to have a lot of relationships. Databases are designed to manage very large amounts of data though, so it should be OK.

Thanks Paul and Tim
I will try to implement it in my project.
Thanks again.