0

This is a typical situation for 1 to many relationships: a chat group iOS app, a group table to record all the group chat related information, like group id, create time, thread title, etc.

To record the participants, of course, I would assume there is another 1:m table. So I was rather surprised to see the app just added another column called "participants" to record it, with each participant is separated by a delimiter (':' to be exact). The problem with that is quite obvious, mixing application code with sql code, e.g. no way to see how many groups a specific user is in with sql code, violated 1NF/2NF, etc.

But they said we understood all your points. But

  1. as this is a mobile app, you always need to use objective c code to access sqlite tables, you won't use sql codes alone. So not a "big deal" to mix them together.
  2. participants don't change often and normally are set when a group is created. If we have 100 participants we would rather just insert 1 record to group table instead of insert 100 records into another group-participants table.

The participant data will be used when someone wants to see who are in this chat group (by several taps on the menu) and when someone joins or leaves the chat group, assume it won't happen often.

So my question is in this particular situation what is the advantage I will gain if I use another 1:m table?

----- update -----

Except for the answer I got, Renzo kindly pointed this discussion to me, which is also very helpful!

Community
  • 1
  • 1
Qiulang
  • 10,295
  • 11
  • 80
  • 129
  • 3
    You get all the benefits of normalization. Storing data in CSV format usually isn't desirable because it makes that data hard (and potentially slow) to query and update. Maybe if they only ever need to get all users at a time it isn't so bad. – Tim Biegeleisen Mar 17 '17 at 07:29
  • Thanks for the comments. But as I said in my question what is the advantage in this particular case, e.g. can you be more specific about "the benefits of normalization: here? – Qiulang Mar 17 '17 at 07:31
  • 1
    We can't answer this without knowing how they use the CSV data. Rapid updates of user rosters in chat rooms should be slower (I think) with CSV data versus maintaining a 1:N table – Tim Biegeleisen Mar 17 '17 at 07:34
  • Add the information about when they will be used. – Qiulang Mar 17 '17 at 07:42
  • `participants don't change often` ... under this assumption, the penalty for storing CSV is less, because this means infrequent updates. – Tim Biegeleisen Mar 17 '17 at 07:51
  • Then back to my question, what is the advantage in this particular case. I need to some "real" example to persuade them. – Qiulang Mar 17 '17 at 07:55
  • There are 1000 users in a room, but let's assume that it is very rare that this number, or roster, ever changes. If stored as CSV, you can do a simple query and return a single record with all users. In the 1:N case, you would either have to aggregate on the server and return (a penalty), or return 1000 rows, which might have more overhead than returning a single row with the same content. – Tim Biegeleisen Mar 17 '17 at 07:57
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/138304/discussion-between-qiulang-and-tim-biegeleisen). – Qiulang Mar 17 '17 at 08:05

1 Answers1

3

It's hard to respond to "is this design better/worse" style questions without understanding the full context. I'm going to make some assumptions based on your question.

You appear to be building a mobile application, supporting "many to many" user chat. I'm picturing something like Slack.

Your application design is using the SQLite database for local storage.

Your local sqlite database on the phone is some kind of subset of the overall application data - like a cache, only showing the data for the current user.

If all that is true, the question really is down to style/maintainability on the one hand, and performance and scalability on the other.

From a "style" point of view, storing the data in a comma-separated value in a column is ugly. A new developer who joins the project, with a background in "regular" database design will consider it at best a hack. On the other hand, iOS developers may consider it perfectly normal.

From a performance point of view, it's probably not worth arguing about - parsing the CSV is probably just as slow as reading/writing from the database.

From a scalability point of view, you may have a problem. If the application design needs to capture in which order users joined the chat, or capture some kind of status (active/asleep, for instance), or provide a bit of history (user x exited at 21:20), you almost certainly end up re-designing the database.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
  • All your assumptions are correct. I am going to use your words "If the application design needs to capture in which order users joined the chat, or capture some kind of status" to convince them. So I mark your answer accepted. Thanks! – Qiulang Mar 17 '17 at 08:00