3

Lets say a user has a set of data. This data is stored in user_data table. This user_data table is referenced by other users in some way. In case the user wants to delete a row from the user_data table, referential integrity dictates that it cannot be done as long as there exist a reference to that data.

Let me explain further by giving you an example of the type of data that user can store and are referenced by other users - a simplified video streaming service. Some data to be stored:

user_videos

id
video_name
video_url
owner_id

video_playlist

id
name
owner_id

playlist_items

playlist_id
video_id

So from the above example, a user can have videos that can be referenced in playlists which can be created by other users. If referential integrity is enforced, then user won't be able to delete the video unless there are no further references (on delete cascade).

How should I implement the database to handle the deletion?

Option 1: Do not enforce referential integrity. Playlists may refer to videos that do not exist.

Option 2: Hide user video data by introducing a "deleted" field.

Option 3: Assign video to a special user that takes ownership of all orphaned videos.

Option 4: On delete cascade and delete all playlist that references the video.

I'm leaning to towards option 1, but I've always been told to enforce a foreign key constraint. But in the case of interlinking user data as per my example above, could a foreign key be counter intuitive?

Improve this question
3
  • 1
    Which option most effectively satisfies your software requirements?
    – Robert Harvey
    Jan 25, 2016 at 2:56
  • Option 1, no referential integrity. But isn't that bad database design? I'm not too great at it yet and I don't want there to be some problems later down the line because poor me, I did not enforce referential integrity in my early career.
    – user3804927
    Jan 25, 2016 at 3:04
  • 4
    What do you want the users to experience when they use a playlist with deleted videos? Should they see an error? Should the player automatically skip the deleted video but still display it in the list? Should deleted videos not appear in playlists? Answer these questions and you'll probably find what makes sense to enforce at the database level.
    – MetaFight
    Jan 25, 2016 at 3:21

1 Answer 1

Reset to default
3

My suggestion is similar to #3 but with a difference.

While in #3 su suggest not deleting the video at all but moving it to another user who will be the owner of all orphaned videos, I suggest this:

  1. A special video called "deleted video" should exist, which owner is "Deleted video owner". That special video's ID is, say, 1.
  2. As PLAYLIST_ITEM is a many-to-many table between USER_VIDEO and VIDEO_PLAYLIST (entity names should be singular), when a user deletes a video the program first updates the rows in PLAYLIST_ITEM that reference that video and changes them to reference video ID 1 ("deleted video").
  3. Insert into a table called DELETED_VIDEO_COMMENT a comment like "Happy Kitten video deleted by owner". That would be a one-to-one table with a foreign key to PLAYLIST_ITEM, that would only containt a row explaining the name of the deleted, previosly referenced video.
  4. After all references to the video to be deleted are gone, then delete it.

Advantages:

  1. Playlist owners don't see their playlists shortened without apparent reason.
  2. Playlist owners see a "deleted video" among their playlist items or even better "Happy Kitten (deleted by owner)".

ADDENDUM.

When playlist owners purge deleted videos from their playlist, the row in DELETED_VIDEO_COMMENT goes with them.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.