0

I need to store experimental matrices in a database. I started by compiling an ERD and ran into a problem. The tables "Matrix" and "Matrix_element" must be required (this is a requirement). The problem is that the matrices for experiments have string names, so I would like to have access by name, and not by id. At the same time, it seems not correct to replace "matrix_id" with a string type, because the names can be quite long (something like "TempNreg410"), while the matrix size can be 5000x60000, and there are hundreds of such matrices. It turns out the repetition of a long line a huge number of times. Please advise how to proceed. Below are 2 diagrams that I have been able to come up with so far.

enter image description here

Chris Albert
  • 2,462
  • 8
  • 27
  • 31
Ли Шеньшунь
  • 13
  • 3
  • For starters, a table cannot have more than 1 primary key, which is violated in your ERD – Rinkesh P May 12 '22 at 12:01
  • @rinkeshP it is possible that they are [composite key](https://stackoverflow.com/questions/1110349/how-can-i-define-a-composite-primary-key-in-sql). – Bagus Tesa May 12 '22 at 14:29
  • is the matrix name had to be `unique`? you can use int for primary key to save space and then have column name set as `unique` index. [plenty of discussion](https://stackoverflow.com/questions/487314/primary-key-or-unique-index) that you can use as reference. – Bagus Tesa May 12 '22 at 14:34
  • @BagusTesa Yes, the string name will be unique. If I understand you correctly, you mean something like [this](https://i.ibb.co/b7zt3pL/Untitled-Diagram-4-drawio.png). It seems to be quite logical, because in this case, the string name of the matrix can be Null, and if I'm not mistaken, 1NF-3NF are not violated. The only thing that confuses me for some reason is that in this case the query is not by the primary key, but by the name, `select * from Matrix where name = "some "`. Please tell me if this is bad manners or not? – Ли Шеньшунь May 12 '22 at 20:00
  • @BagusTesa yes, so one should mention composite key as CK. Just for the correctness of ERD – Rinkesh P May 13 '22 at 04:22

0 Answers0