Table rows vs. comma separated

Genbox

I'm creating a database that can send private messages. The system utilized MSSQL database engine and uses relations to constrain the data inserted. Problem is that users can send mass-messages and that can add up quickly if each row in a table represents a message to a user. My question is: Is it better use one row per message or use a comma separated list of users? I would think that the table with the ID's of the message and users (many-to-many relation table) can become REALLY large quickly. A comma separated list would keep the number of rows down.. What is more effective?

Member 4501940

assuming that the recipient list are also users User Table Columns UserID+User Message Table Columns MessageID+Message Sent Table Columns UserID+MessageID (using the senders user id) Receive Table Columns UserID+MessageID (using the recipients user ids) use csv if recipients are not users or you don't need to track don't use csv if you intend to parse later - not effective

Genbox

Your proposal is actually very close to my current database schema. As for the CSV, If the users need to delete a mass-message, I need to keep a list of unique receivers. This can be represented by rows or CVS. I need to edit the CVS in case a user delete a message. I need to remove a row in case I use rows. It is simply a matter of what is the most effective method after a (long) period of time. I would guess that a table with >1.000.000 rows would take a long time for the database to process. Then I rather have to use a little more memory and CPU time on parsing a comma separated list. Any experiences on this matter? Any good db schemas I can look at? (I'm thinking forums here).

Member 4501940

Yes, lots of experience. Just finished a db optimization contract(job). The client was adding a million records every couple of days. Have been doing the same for 20+ years. The Receive Table may have a lot of records in it but only 2 columns and those columns will make up the primary key. To query for a given record, you will use the pk. Clustered, indexed, primary key 2 columns only - should be able to get any given record in about 3 milliseconds or less with 10 million records in it. Subtract a ms or 2 if your id values are numeric. This type of table will not take up much space. This will be thousands of times faster than searching for a partial string value in text where you will won't be able to find the user by using an index - at all. (not easily anyway) Searching for a string in a text type field is a VERY time consuming operation for a db to do - not a good idea. I would create test scripts and data to validate if you're not convinced. Let me know if you need help. What db are you using?

Genbox

Sounds great. I don't have a lot of experience on the inner workings of SQL databases, so I was not sure what the best approach was. I will build the database without CSV and hope the database is clever enough to find a single (or range of) row within millions of rows without any performance problems. The current database schema works exactly like that (2 PK) on numeric datatypes. And you are right, somehow I did not take the partial text matching into account. That would take ages. That alone would keep me from using CVS. As a side note, the database is a MSSQL 2008 Express engine. Thanks for your support. I really appreciate it.

David Skelly

If your problem is the number of rows, one million rows is not large for a modern RDBMS. If it is indexed properly, it will cope with that with absolutely no problem in terms of simple queries or updates. Things get a bit hairier if you're doing more complex manipulations, using cursors or anything like that, or doing wildcard queries like '%XYZ%', that sort of thing.

Genbox

The database schema is created in such a way that data that needs complex queries are placed in their own tables. The table in question is a 2 column composite key table. From what I've gathered here, the engine should have no trouble doing simple queries against several million rows. Thanks for the clarification though. Does any of you by chance have any good articles on database optimization and inner workings of RDBMS?