What kind of relationship I have among database tables if the link between two fields is not enough to identify a record?

Question

I have a MySQL database with several tables. Most tables contain data and both translatable and non-translatable texts.

When a table contains a text that can be translated to one or more languages, I do not store the text in that table, but I use an id referring to another table that contains all the possible translations for all the possible texts. Let us make a practical example:

Table USERS
user_id BIGINT(20) UNIQUE PRIMARY KEY
user_name VARCHAR(190)
user_surname VARCHAR(190)
user_age INT
user_motto_id BIGINT(20)

The user's name and surname are NOT translatable, so they are stored in the table. The user's motto can be translated, so I store in table only an id that correspond to the real user's motto. To identify the translation to retrieve, however, I use ALSO the language code dynamically returned by get_locale(). So user_motto_id corresponds to text_id, NOT trans_id. This is important.

Table TEXTS
trans_id BIGINT(20) UNIQUE PRIMARY KEY
text_id BIGINT(20)
lang_code VARCHAR(7)
text LONGTEXT

So, for example

Table USERS
1 | Joe | Doe | 25 | 101
2 | Mary | Foo | 31 | 107

Table TEXTS
1 | 101 | en_US | Raise your hearts
2 | 101 | it_IT | In alto i cuori
3 | 101 | fr_FR | Élevez vos cœurs
4 | 107 | en_US | To the stars and beyond
5 | 107 | it_IT | Fino alle stelle e oltre
6 | 107 | fr_FR | Vers les étoiles et au-delà

I am using MySQL Workbench to model the database. In my EER diagram no connection is visible between TEXTS and USERS because text_id is not enough to identify which translation should be used. I also need the lang_code that I obtain at run-time.

However I would like to make it visible that some connection exists, in some way, but how? Is there a way to say that user_motto_id is a value that should correspond to some text_id, even if it is not enough to retrieve the record I need?

Answer 1

If I understand your description correctly. You must enter user_motto_id in the USERS table as an foreign key and connect to the TEXTS table.

Like the example below:

CREATE TABLE categories(
    categoryId INT AUTO_INCREMENT PRIMARY KEY,
    categoryName VARCHAR(100) NOT NULL
)ENGINE=INNODB;

CREATE TABLE products(
    productId INT AUTO_INCREMENT PRIMARY KEY,
    productName varchar(100) not null,
    categoryId INT,
    CONSTRAINT fk_category
    FOREIGN KEY (categoryId) 
        REFERENCES categories(categoryId)
        ON UPDATE SET NULL
        ON DELETE SET NULL 
)ENGINE=INNODB;

For better information, read the following link:

https://www.mysqltutorial.org/mysql-foreign-key/