CiviCRM Community Forums (archive)

In Peter Haight's October blog post on his Doctrine work and the resulting discussion a few items came up that I consider to be opportunities for schema cleanup as we move to Doctrine. I'm posting here to see if we can deliberately brainstorm for ideas about how we might want to refactor the schema during the move to Doctrine. To keep things focussed, I'd suggest we exclude things not related to schema refactoring, except insofar as they are necessary to support a particular refactoring or pattern of refactoring.

1. cleanup oddities like not using a unique numeric foreign key for things like payment processor reference.
2. Do some research on patterns to replace the entity_table / entity_id way of referring to foreign keys. The main pro of that pattern is that it is a simple way to implement a reference to 2-n foreign tables with just two fields. Cons include a) the fact that we need to code the maintenance of the references, and b) we cannot take advantage of MySQL's integrity constraint checking and enforcement. These are system quality and code maintenance issues. Less important cons are c) i suspect coders need to do more work to optimize query performance, and d) We may lose some of the convenience that is provided for foreign keys in the ORM, or at least have to do more work to get something close to it.

Other opportunities are more forward looking. Namely, Doctrine supports new field types thatwe for the most part have not used:
object: Type that maps a SQL CLOB to a PHP object using serialize() and unserialize()
array: Type that maps a SQL CLOB to a PHP array using serialize() and unserialize()
simple_array: Type that maps a SQL CLOB to a PHP array using implode() and explode(), with a comma as delimiter. IMPORTANT Only use this type if you are sure that your values cannot contain a ”,”.
json_array: Type that maps a SQL CLOB to a PHP array using json_encode() and json_decode()

anyway, just want to provoke some discussion.

cheers.

Don't get me going on the design by committee that was the CiviAccounts schema changes! In 5his case I think dgg wanted it to make it more similar to the past iirc. I would be pleased to drop it.

I'm keen on storing array-type data as json - either because it seems more modern & elegant - or because I have been brain-washed by the francophone.

WRT the duplication of financial_type_id - I think that CiviAccounts was a massive endeavour & inevitably some of the choices made will have been wrong. It would be good to document some of the ones that you have doubts about Joe so that we are in a better position to review them & hopefully over time simplify a few things around the margins. I would tend to agree with both your comments that the financial_type_id is not required in the civicrm_contribution table - but in order to remove it we would need to ensure the api still returned it (at least if there is only one line item - I think an array for multiple is over-kill).

Of course simplifications are rarely funded - but having an eye on what they will be will make it easier to build them into other pieces of work.

So as not to hijack this thread can I suggest that if you do feel you can document those things you reply here only with a link to the right place to discuss those specific to Accounts.


Another example of the entity_id :: entity_table pattern is the source_record_id field in civicrm_activity.

Does Doctrine offer us anything around logging or multilingual? We don't really use DAO for those so probably not

@eileen, bravo et vive le json libre!

@joe, how do you see replacing entity_id/table for the tags (ie. something that can be applied to case or contacts or activities...)?

Something tangential to the entity_id/table and sql integrity: shouldn't we move to one table by custom group (for the core groups at least?) For why I understand, it's mostly a workaround to the lack of solution to easily have an UI to manage a list of "simple pseudo constants". It would implode the number of entities, but not a problem IMO.
added bonus, we won't have a join between an int field and a string optionvalue that has terrible performance.

X+