Primary and foreign keys

[stanstrup]

Wouldn't it make sense to set the compound_id in the compound table as a primary key and unique?

Then synonym could refer to the key. All other additional tables added with new features would require the foreign key reference.

I don't know if it has many practical applications but I like the idea of having this explicit and potential error if tables are added without proper reference.

[jorainer]

The reason I did not made compound_id unique is that the compound identifiers from some databases are not unique.

Note however that synonym does in fact already point to that column, i.e. the reference from synonym to compound is via compound_id, also from msms_spectrum we link to compound via compound_id.

I would not like to force primary/foreign key constraints to database columns to keep the design of the tables as simple as possible... but we can discuss on that obviously.

[stanstrup]

OK. But if compound_id is not unique how do you refer back to it in a unique way? One spectrum would then refer to several compounds?

Another way to do it would require/generate a unique ID, use that for reference, and then have another column with the external ID?

[michaelwitting]

What about the use case that somebody wants to generate a "superduper merged DB" from HMDB, ChEBI, KEGG etc. Then the compound_id would be mixed as far as I got it. Is there the possibility to add IDs from different DBs as additional columns? I usually prefer the InChIKey as unique ID for metabolites (also not perfect, but the best that could be done so far). In my SQLite DBs for masstrixR I just have a running integer as unique ID within the DB.

[jorainer]

In the end, the compound_id is supposed to be unique - I just did not put the SQL contraint on the column to force it to be unique or a primary key.

I also thought of using the InChiKey as primary identifier, but that does not work out, as even HMDB has several compounds with the same InChiKEy, but different compound ids.

If you want we can force the compound id to be unique in the compound table, no problem with that.

[jmbadia]

Allow me to share my experience. Making up my own merged database (HMDB+MoNA+Nist+, ...) I used two variables: compound_id (primary key) and old_compound_id (primigenius id, for traceability reasons).
I use also that strategy for spectrum data structure, (spectrum_id (primary key) & old_spectra_id). Here spectrum_id allows me also to link every spectrum matrix (keep it as a list apart of matrices ) with its metadata spectra (a spectrum (metadata) dataframe)

[jorainer]

Thanks for the feedback @jmbadia ! And based on which identifier/information you merge compounds across databases? Is it the InChiKey?

[jmbadia]

Yes, and it is something our group still talk about because we know we have under the same 'compound' different isomers (and it hurts). But at the end we have assumed a very pragmatic approach:

A) It is a information that we just can not have. The only way to differentiate among compounds in a DB would be considering "isomeric smiles" (not available usually) or "name" (very inconsistent) values. So actually it would be very difficult to know which isomer is every compound's DB
B) We assume that we are working with "a compound... and its isomers" . The user knows that the result of an identification is not referred to a compound but also to its isomers. We only trust its inchikey.

Obviously, we might as well not have merged compounds

[jmbadia]

BTW we also merge spectra, but it is no so controversial. Same (relative) spectral matrix & same nuclear spectral metadata=> same spectra