I'll have to look into it. I'm more familiar with PubChem.

What kid of database do they use in their back end? I downloaded some substance from them and I didn't find any db dump or schema on their FTP, just XML files. It would be awesome to know their database and data model. I also read some of their publications but they did not detail this part of their solution.

I don't see chembl_id_lookup in their schema. Which table.column are you referring to?

They use an RDBMS. I believe the myChEMBL dumps are for MySQL. Link to schema was in my prior comment.

@MichaelKutz It is in the "GENERAL" (grey) rectangle in the top-middle part on the diagram what you linked. One of its column is "entity_type".

Yes they use an RDBMS for ChEMBL. I referred to PubChem and Entrez when I said I don't known the database technology in use.

Long gone are the days of using proprietary DB Technologies for storing bioinformatic/cheminformatics Even then, I believe the MDL ISIS/Server used Oracle as its back-end. (MDL moved to the MDL Direct data cartridge for indexing chemical structures)

I agree I use PostgreSQL with RDKit cartridge to represent molecules and their fingerprints.

Now I am having a meeting, in a hour (hopefully) I'll write further details

I've been out of the Pharma industry for 6+ years. MSI turned to Accelrys near the time I started. Accelrys merged with MDL (to form Biovia - iirc) just before I left.

Thank you. I checked on MDL, yes it uses Oracle. It is somewhat similar to our system, but not entirely. However it is unlikely that I can persuade my supervisors to buy it.

My original question however is not specific to pharmacology. It is a valid problem in any research oriented OLAP data model, which plans to provide universal identifiers externally and use integer surrogates for strong entities.

But, aside I am really happy to meet someone with your experience in this field, because in my company I am the most experienced in biomedical data and knowledge engineering, which is a problem because I do not consider myself experienced enough.