There is a database with two tables

enter image description here

How to set attributes in 1 table

That is, if you have to say 3 Authors and 1 Keywords per 1 document, then this should look like

Document_id | index | Authors | Keywords

........ 1 ............ | ..... 1 .... | .BlaaBla .. | | Blabla

........ 1 ............ | ..... 2 .... | .BlaaBla1 .. | | Blabla1

........ 1 ............ | ..... 3 .... | .BlaaBla2 .. | | null

How to implement it correctly?

  • Yes, as necessary, and implement. This is determined by the actual logic of the process, and not by the program implementation. - Akina
  • one
    since you need to implement it in your picture, the primary key can be composite, and the rest is standard. - Eugene Bartosh

1 answer 1

CREATE TABLE document_r ( document_id int NOT NULL, index int NOT NULL, authors varchar(255), keywords varchar(255), PRIMARY KEY (document_id,index), FOREIGN KEY (document_id) REFERENCES document_s(document_id) )
  • And the index should be incremented on each id again or is it not necessary? - Eugene
  • the document_id + index combination should be unique in the table, as you will get to the database anyway, it simply does not insert 2 records with the same primary key - Eugene Bartosh
  • Thank you for the clear answer - Eugene
  • you can create sequences on the auto-increment of fields - see the syntax in the dock, or here is an example tutorialspoint.com/mysql/mysql-using-sequences.htm - Eugene Bartosh
  • One nuance appeared. I need the index values ​​to be in a certain range, that is, 1 id has 2 authors and 3 keyworda, then the number of entries should be MAX [2,3] = 3, that is, the indices per 1 id Must Be 1, 2, 3 - Eugene