How to use one to many relation?-CodePudding

I have a table contains all transaltions of word:

CREATE TABLE `localtexts` (
  `Id` int(11) NOT NULL,
  `Lang` char(2) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'ru',
  `Text` varchar(300) DEFAULT NULL,
  `ShortText` varchar(100) NOT NULL,
  `DbVersion` timestamp NOT NULL DEFAULT current_timestamp(),
  `Status` int(11) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Also there is one of table:

CREATE TABLE `occasions` (
  `Status` tinyint(3) UNSIGNED NOT NULL DEFAULT 1,
  `Id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    CREATE TABLE `compose` (
  `Status` tinyint(3) UNSIGNED NOT NULL DEFAULT 1,
  `Id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

So, occasions table has many translates. It means one to many relation. The same situation with compose.

But problem is localtexts should have foreign key to occasions.Id and compose.Id and so on.

How to use one to many properly?

CodePudding user response：

You should do this the other way round: occasions and compose should each reference the text table. But you'll need one parent table for all texts without languages because you don't want to reference a specific translation.

Create the table texts for all texts, regardless of the language.

CREATE TABLE texts
             (id integer(11),
              PRIMARY KEY (id));

Amend localtexts: Add a primary key and a reference to texts.

ALTER TABLE localtexts
            ADD PRIMARY KEY (id,
                             lang);

ALTER TABLE localtexts
            ADD FOREIGN KEY (id)
                REFERENCES texts
                           (id);

Amend occasions and compose: Add primary keys, and a reference to texts.

ALTER TABLE occasions
            ADD text integer(11);

ALTER TABLE occasions
            ADD PRIMARY KEY (id);

ALTER TABLE occasions
            ADD FOREIGN KEY (text)
                            REFERENCES texts
                                       (id);

ALTER TABLE compose
            ADD text integer(11);

ALTER TABLE compose
            ADD PRIMARY KEY (id);

ALTER TABLE compose
            ADD FOREIGN KEY (text)
                            REFERENCES texts
                                       (id);

Edit:

Or, if you want to refer to a specific translation of a text, you don't need the extra table. Just reference localtexts.id.

Amend localtexts: Add a primary key:

ALTER TABLE localtexts
            ADD PRIMARY KEY (id);

Amend occasions and compose: Add primary keys, and a reference to localtexts.

ALTER TABLE occasions
            ADD localtext integer(11);

ALTER TABLE occasions
            ADD PRIMARY KEY (id);

ALTER TABLE occasions
            ADD FOREIGN KEY (localtext)
                            REFERENCES localtexts
                                       (id);

ALTER TABLE compose
            ADD localtext integer(11);

ALTER TABLE compose
            ADD PRIMARY KEY (id);

ALTER TABLE compose
            ADD FOREIGN KEY (localtext)
                            REFERENCES localtexts
                                       (id);