search

ispyb / ispyb-database-modeling

4 stars 3 forks source link

Schema Fixing: Consolidate *program tables #32

Open stufisher opened 6 years ago

stufisher commented 6 years ago

I would like to suggest consolidating the program/programattachment tables. At the moment there is various duplication of these tables across the schema. I would suggest AutoProcProgram is used by any table that runs a "Process", and attachments are added to AutoProcProgramAttachment. This then makes the database look as follows:

autoprocprogram_consolidation

We can deprecate phasingprogram/attachment, and make screening also link to this, and thus store attachments for any process in a central location = easy sql

KarlLevik commented 6 years ago

This fits well with my proposal/argument for incremental schema fixing. (See other tickets prefixed with "schema fixing".)

stufisher commented 6 years ago

There is also a question about how autoprocprogram links to other things. It is tempting to put the foreign key to datacollection in here, and remove it from AutoProcIntegration (the whole autoproc tables could be consolidated too, but thats another ticket)

KarlLevik commented 5 years ago

@stufisher Is this still how you'd prefer to do this? How do the ProcessingJob tables fit into this?

Also, re: easy sql, how are you planning to query the tables, i.e what joins do you expect in the query to display processing statuses / results?

delageniere commented 4 years ago

At Soleil September 12th 2019 meeting: decided to remove the PhasingProgramRun and PhasingProgramAttachment tables to replace them by AutoProcProgram and AutoProcProgramAttachment.