1

What is the best way to link two tables together if there is a creator and owner relationship between them? For example a user create a media file entry. A user optionally has an image / media file as a profile picture. I've built a schema with MySQL Workbench. But that way it doesn't work. The other tables: A project has one or many bugs, a bug has one or many comments. Both bugs an comments can have one or many images / media files. Thanks!

Mysql workbench wrong database schema

Mike
  • 161
  • 1
  • 12
  • Can a media be used in one bug or one comment? Or it can be used in many bugs or comments too? – Gholamali Irani Feb 02 '18 at 08:50
  • At first, no media library is planned. But in the future I think it would make sense that you can reuse media in bugs and comments. So I used here a n:m relationship. But don't know how to the user <-> media relationship can be realised. – Mike Feb 02 '18 at 08:59

1 Answers1

1

Firstly:
Based on you explanation, you Database Design should be like this (UML notation):

enter image description here

a user create a media file entry.

So you have a creator_user_id as F.K into media. It can not be null and each media should have a creator.

A user optionally has an image / media file as a profile picture.

So you have one-to-one relationship between user and media. You have two options:

  1. move user id as F.K to media as profile_user_id and make it Allow NULL and Unique.
  2. move media id as F.K to user as profile_media_id and make it Allow NULL and Unique.

Both is correct.

For example (second option), you can have a profile_media_id as F.K into user. It can be null. But it should be unique. Means that a media can be appears only in one user profile.

This design (second option) is not wrong. It looks a circular reference. But, when you are adding a new user with a profile image (at the same time), you should perform these actions in one transaction:

  1. create a user (and let the profile_media_id to be null)
  2. create a media with new creator_user_id
  3. update the new user profile_media_id


Secondly:
Your second parts of design (relationships between media and post and bug) looks good. (based on your explanations)

However, I offer designing a whole external media management system for them (see my answer here).

EDIT

you can use something like below to create your tables:

SET FOREIGN_KEY_CHECKS=0; 
DROP TABLE IF EXISTS myuser;
SET FOREIGN_KEY_CHECKS=1;

CREATE TABLE myuser(
    user_id INT NOT NULL,
    profile_media_id INT DEFAULT NULL,
    PRIMARY KEY (user_id)
);

DROP TABLE IF EXISTS mymedia;
CREATE TABLE mymedia(
    media_id INT NOT NULL,
    creator_user_id INT NOT NULL,
    PRIMARY KEY (media_id),
    FOREIGN KEY (creator_user_id) REFERENCES myuser (user_id),
    UNIQUE (creator_user_id)
);

ALTER TABLE myuser ADD CONSTRAINT myusermymediafk FOREIGN KEY (profile_media_id) REFERENCES mymedia (media_id);

See MySQL test here.

Gholamali Irani
  • 4,391
  • 6
  • 28
  • 59
  • Hello Gholamali-Irani, I tried it this way on my MariaDB (Xampp via phpMyAdmin import). But here the create table statement issue an error: Can't create table `dbname`.`user` (Fehler: 150 "Foreign key constraint is incorrectly formed"). Referenced table `dbname`.`media` not found in the data dictionary near. So I can't create this schema because there reciprocal relationship. Is there an other clean way for MySQL / MariaDB - or have I to use n:m schema or delete the creator user logging field? – Mike Feb 03 '18 at 02:08
  • 1
    Please see the example added. – Gholamali Irani Feb 03 '18 at 08:08