På fredag 02. mai 2014 kl. 00:34:34, skrev Tomas Vondra <t...@fuzzy.cz <mailto:t...@fuzzy.cz>>: On 1.5.2014 23:58, Andreas Joseph Krogh wrote: > På torsdag 01. mai 2014 kl. 23:45:49, skrev Tomas Vondra <t...@fuzzy.cz > <mailto:t...@fuzzy.cz>>: > > On 1.5.2014 23:19, Andreas Joseph Krogh wrote: > > Just curious: > > Is such a JOIN index possible in other DBs, if so - which? > > Can other DBs do index on difference between two sets? > > Will PG ever have this, is it even possible? > > I'm not aware of such database, but maybe it's possible at least for > some cases. But I'd expect that to significantly depend on the schema. > And I'm not aware of any such effort in case of PostgreSQL, do don't > hold your breath. > > IMHO the problem with your schema is that while each 'read' message has > a matching row in message_property, 'undread' messages may or may not > have a matching row. Is there a particular reason for that? > > > > Yes. The point is that maintaining a message_property pair for all > messages for all users in the system imposes quite a > maintainance-headache. As the schema is now any new message is per > definition un-read, and when a user reads it then it gets an entry with > is_read=true in message_property. This table holds other properties too. > This way I'm avoiding having to book-keep so much when a new message > arrives and when a new user is created. A message in my system does not > necessarily have only one recipient, it might have one, many or none, > and might be visible to many.
So how do you determine who's the recipient of a message? Or is that the case that everyone can read everything (which is why you're displaying them the unread messages, right)? A message might have a recipient and might be read by others. I understand you're trying to solve this without storing a row for each possible message-person combination, but won't this eventually happen anyway (with is_read=true for all rows)? I will end up with that only if all users read all messages, which is not nearly the case. -- Andreas Jospeh Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>
