guyjacks · May 3, 2018 04:52
diff --git a/generic_relations.yaml b/generic_relations.yaml
 Problems:
 	Performance:
 	- extra lookups to perform joins
 	- extra lookups to manually check foreign key integrity
 	- Requires more memory for indexing on entity type
 	Complexity:
 	- The application must enforce foreign key integrity
 	Maintainability:
 	- It will be difficult to reason about the schema without examining records to determine which entity types may be referenced
 	- New applications talking to the db will also need to enforce fk integrity and may do it wrong.
 	Flexibility: 
 	- Simple M2M tables containing t1.id and t2.id can be generalized, but what happens when we need additional fields in the future?  
  	- For instance, the m2m table relating treatments to biopsies currently only contains a biopsy_id and a treatment_id.  Trevor has already asked us to add new fields to that M2M table.  If we had used a generic table then we would be SOL.  I suppose we could use a json field, but that's limiting in that we may need to add fields that require fk enforcement.  This is already a problem, because Trevor also mentioned that we may need to add information about a regimen and a clinical trial to the m2m for biopsies and treatments.  In that case, the json field would not work.
	Problems:
	Performance:
	- extra lookups to perform joins
	- extra lookups to manually check foreign key integrity
	- Requires more memory for indexing on entity type
	Complexity:
	- The application must enforce foreign key integrity
	Maintainability:
	- It will be difficult to reason about the schema without examining records to determine which entity types may be referenced
	- New applications talking to the db will also need to enforce fk integrity and may do it wrong.
	Flexibility:
	- Simple M2M tables containing t1.id and t2.id can be generalized, but what happens when we need additional fields in the future?
	- For instance, the m2m table relating treatments to biopsies currently only contains a biopsy_id and a treatment_id. Trevor has already asked us to add new fields to that M2M table. If we had used a generic table then we would be SOL. I suppose we could use a json field, but that's limiting in that we may need to add fields that require fk enforcement. This is already a problem, because Trevor also mentioned that we may need to add information about a regimen and a clinical trial to the m2m for biopsies and treatments. In that case, the json field would not work.