How to reference several object of one table into another

sorry if the question was already answered.

Situation: A set of compounds are associated with a list of one or more bibliographic references. I have the idea that one table shall contain the chemical structures plus keys to another bibliographic table containing the references.

1/ Do this structure of database if possible?

2/ Any clue how to implement it? Is it possible with InstantJChem?

Thanks in advance,

Gilles Marcou

Dear Gilles,

yes, this is certainly possible as Instant JChem relies on relational database model underneath it. 

The situation that you describe is fitting for a 1:N (one to many) relational model, so what you basically need is:

• 2 tables (lets say Structures and Bibs)


• Relationship set up between those 2 tables (also known as a foreign key in database terminology)


• Structures table will be the "one" part of the relationship


• Bibs table will be the "many" part of the relationship

Please consult this tutorial in the documentation for more details here.

Wishing all the best

David

Thanks for your answer David. Reading you I realized I was describing a many-to-many relationship, that implies setting up a join table. A table Subs contains the compounds, a table Ref contains the references and of course, a table SubsRef must be defined as the join table. It maps to the foreign keys of Subs and to the foreign keys of Ref.

Thanks again.

Gilles Marcou

Dear Gilles,

you are welcome. Regarding the join table, if you use our Many-to-Many relationship settings wizard, the join table will be created automatically under the hood for your information.

Wishing all the best

David

Hi David,

you're right, the many-to-many option creates the join table behind the scene. But, I think it is difficult to feed the structure. Either of two cases:

- the database structure is build first and data are added next.

- the database tables are already there and feeding the automatically created table requires SQL commands (dangerous and difficult).

Feeding the tables with data from files, in my opinion, is more easy if the n:n relationship is emulated by the 1:n|n:1 relationships. But maybe i'm wrong, do you know a smarter option?

However emulating the n:n relationship allows building intuitive formular too.

Ciao,

Gilles Marcou

Dear Gilles,

the best solution in my opinion would be to create the tables with the desired data by importing it from files and then adding the relationship between the tables manually via IJC. 

Basically, as you suggest as the easiest way.

The other way with building the database structure first and then importing the data will also work and our customers rely on both ways, but the second option might be more challenging. 

Wishing all the best

David

Hi David,

thanks for your insight.

Ciao,

Gilles Marcou