3

I'm doing some research on how to use Vaadin+JPA+EclipseLink+PostreSQL for the development of a web application for sharing pictures and how well is this combination suited for this purpose. I'm struggling with the decision on the management of user contacts. I will go into further detail on this after I explain the structure of my tables:

Users table

The users table consists of the following columns:

  • User ID - automatically generated upon registration (unique)
  • Username - the name of the user; defined upon registration (unique)
  • Password - the password of the user; defined upon registration
  • Uploads - all the IDs of images that the user has uploaded. I will not go into detail here because this doesn't concern my problem. Suffice to say that this column is used together with another table called Images for managing the gallery of the current user

Contacts table

This table is purely for storing relation information between the users (a colleague of mine decided to do that, which I frankly don't like but it's already done...). It has only two columns:

  • User ID - the user's ID who has the contacts (see below)

  • Contacts - stores all user IDs that the user with User ID has given permission to to view his image gallery.

    Note: this is not a bidirectional relation. This means that when user X adds user Y to his contacts, user X grants access to his own image gallery to user Y. However this does not make user Y's gallery visible to user X. This can only happen if user Y also adds user X to his contacts. And image can be either public or private. Public images can be viewed by all users however the private ones can be viewed only by the user who owns those and all others whom the owner has granted access to by adding them to his contacts. "Private" might not be the best term here so you might view it as "allow friends to view it" kind of thing.

Whenever I delete a user I have to do the following:

  1. Delete all image files uploaded by the user - an image in the Images table has a single uploader. The viewing status it's totally safe to simply delete the file and remove its entry from the Images table since each image is bound to its uploader and noone else (affected: Images table, storage)
  2. Delete the user's folder - sadly the way Java works you have to first make sure a folder is empty before you delete it (affected: storage)
  3. Delete user from all contacts lists of all users - THIS HERE is the main dilemma I'm having (affected: Users table)
  4. Delete the user himself (affected: Users table)

Because of the queries I've written so far this order is basically how it is supposed to be. I find it to be the optimal considering the relations between my tables.

As I've mentioned in point 3 I have quite the dilemma on how to do this with as less effort (coding- and performance-wise) as possible. I'm looking into two solutions for this:

  • Unidirectional relation - don't change the Users table and simply travers all users in it and then traverse the contacts of each user looking for the user ID of the current user that I want to delete

  • Bidirectional relation - add a new column to Users called addedBy that stores all user IDs of users who have added the current user that I want to delete to their contacts. Every time user X adds another user to his contacts, that other user "adds" user X to his addedBy list but not contacts.

The problem I'm seeing in the unidirectional relation is exactly the fact that I have to traverse through all users and then check the contacts of each to see whether it contains the user ID I want to remove.

The problem I'm seeing in the bidirectional relation is the overhead that is added with the new column. The big plus here however is that I can skip looking into the contacts of all users (of course worst case here is when the user has been added to the contacts of all users which omho is not likely to happen ;)) and do that only for those who are in the addedBy list.

I'm far from a database expert and as you might have noticed from the written above there is probably a much better way of doing things. However I'm interested in only the two versions I've mentioned above and with as few changes to the DB as possible. Also I don't know which query would be easier to write. I have managed to do all the other steps (1,2 and 4) from the deletion process but number 3 is a tough one.

Any idea which one would be better? Number of users is not important since this is done purely for research however a couple of hundreds/thousands entries in the Users table should make things more realistic.

rbaleksandar
  • 8,713
  • 7
  • 76
  • 161
  • Any reason for keeping user ids in a single column instead of creating something like `permission [from_user_id, to_user_id]` table to keep records of image viewing permissions? – Predrag Maric Oct 01 '15 at 11:25
  • Is an extra table better then putting the same information inside the user's contacts column which allows me to use basic list operations (combined with `EntityManager` to remove/add/change stuff? I also have no idea how to do that `permission [...]` thing you've mentioned in terms of creating queries later on. – rbaleksandar Oct 01 '15 at 12:31
  • It is better, it is the right way to do it because you are keeping referential integrity on the database level where it should be instead of in your code. Nothing complicated actually, one table `permission` with columns `id` (primary key), `from_user_id` (foreign key pointing to user who gave the permission) and `to_user_id` (foreign key pointing to user to whom this permission is given). `User` entity would have a list of `Permission` entities (maybe even two lists, like `myGivenPermissions` and `myReceivedPermisisons`), you would still use basic list operations for manipulating it. – Predrag Maric Oct 01 '15 at 12:40
  • Thanks, will give it a go. Might take me a while though. :D – rbaleksandar Oct 01 '15 at 13:05

1 Answers1

1

I think your colleague had kind of the right approach but did not go all in with it.

The "Contacts table", this table looks like a many-to-many relation table.

But the problem is as you described:

Contacts - stores all user IDs that the user with User ID has given permission to to view his image gallery.

In a real many-to-many relationship table you would have two columns

  • 'user_id', what you already have

  • 'contact_user_id' (naming can be different obviously) but the main point is that you have a single row in this table for each contact.

So when you delete a user and want to remove this user from all contact lists you can simply say:

delete from Contacts where contact_user_id = :userIdOfUserToBeDeleted

As @Predrag Maric also describes this will make your database data more healthy by reference integrity not only on the user_id but also it linked contact_user_id.

This would also help if you using JPA or similar, you could for example have a delete strategy using JPA you can set it to cascading to delete those references. But this is only supported in fully normalised tables.

seba.wagner
  • 3,800
  • 4
  • 28
  • 52