aspose file tools*
The moose likes JDBC and the fly likes Storing a List in Database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Storing a List in Database" Watch "Storing a List in Database" New topic
Author

Storing a List in Database

prakash chauhan
Ranch Hand

Joined: Jun 22, 2007
Posts: 81
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
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


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 ]

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
prakash chauhan
Ranch Hand

Joined: Jun 22, 2007
Posts: 81
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.
Tim LeMaster
Ranch Hand

Joined: Aug 31, 2006
Posts: 226
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.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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.
prakash chauhan
Ranch Hand

Joined: Jun 22, 2007
Posts: 81
Thanks Paul and Tim
I will try to implement it in my project.
Thanks again.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Storing a List in Database
 
Similar Threads
Need Help
whats the best way ?
How can I Insert vector elements into a DB??
Runtime Creating Tables
JTable and sorting