Author Topic: ERD and Data Specs?... (Read 1223 times)

fedly · November 05, 2009, 05:05:37 pm

Hi All,

I am pleased with the Floss Book Documentation and this forum and other information available.

However, I am a heavy duty Database Application Developer.

Civi-CRM is clearly not a trivial schema for its purpose. It is really elegant and well thought out.

However, as a Developer (hence posting under Developer Discussion), I feel that a robust ERD is wanting.

Moreover, I feel that a clear and easy to understand (for the audience of people who are very conversant and comfortable with the Relational Model) "Architectural Theory" documentation is wanting.

Further it would be great if there were some "when you add an individual a record in ThisTable is added, a record in ThatTable is added, and a record in TheOtherTable is added", when you add a second address, "a record in ThisTable is added". When you add an individual and assign it as a Spouse of an existing individual "[something complex] happens with the the Address table(s)".

It is my hunch, considering how elegant Civi-CRM is and how thorough the documentation that I have found is, that I am just not looking in the right place.

Could someone please point me in the right direction?

At this point I am left to backwards engineer:
1. Add an Original individual I know
2. Add two addresses
3. Add his Spouse as an individual (name only)
4. Add Spouse relationship
5. Look in phpMyAdmin trying to see the trail of Primary keys and Foreign keys in the manner in which I know it should be done

... still not sure why I can't see Original's address in the Spouse's Contact Record
... still not sure whether Spouse relationship creates "Household" record on the fly (as I had presumed)
... but since spouses usually share a "Household" why this would not be so, but it is not
... tokens seem to be too well utilized so that I gotta believe a mailing label can say "Mike and Carol Brady"
... and so on

All that stuff.

I am certain that those that built this are smarter than me, just wanting to get a lesson in the "under the hood" stuff without having to resort to backwards engineering.

Thanks in advance for any assistance. (Also, even though I build Client-Server RDBMS Applications for the Web and the Desktop for a living and have for a number of years, I am not always hip to the preferred terminology... so apologies ahead of time.)

Thanks, Brad

Yashodha Chaku · November 05, 2009, 10:26:01 pm

Brad :

Quote

Could someone please point me in the right direction?

You might want to look at :
http://wiki.civicrm.org/confluence/display/CRMDOC/CiviCRM+ERD+2.0
http://civicrm.org/architecture

HTH
-Yashodha

xavier · November 06, 2009, 02:03:33 am

Hi,

Have you seen the ERD ? Mostly up to date and great use. Otherwise, each column has a good description and with fkeys, gives a quite ok idea IMO.

As for what you describe, that isn't about DB, and even if I agree with you that it isn't documented enough, not sure how useful it would be. By the time you have written down the description of what happen when you create a spouse (or doesn't), it might have a new feature that has been added, and the doc will be obsolete, or half of the time is going to be spending describing the code.

Not sure that level of detail isn't better covered by viewing at the code directly.

How, and as your functional expectation, not sure it's a good idea, eg. you can be married but separated and living in different houses.

This being said, the relationship UI in general has room for improvement indeed.

X+

fedly · November 14, 2009, 06:19:03 am

Wow!

Thanks very much!

I am pleased, but not surprised, that I was correct in my supposition that I had not yet have found it -- not that it didn't exist.

I totally understand that the huge benefit to a great deal of and rapid pace of development/enhancement of civiCRM would make the "scenario" style of documentation ephemeral to the point of it not being a useful endeavor.

Then again, I would like to think that the "larger decisions" could be explained without relegating the curious to backwards engineering either by trial and error or by understanding another's code.

However, that is merely a theoretical discussion -- my ignorance is so monumental that I need to first attempt to cure that by digesting the great links from Yashodha.

As an aside, I look forward to a point where I might be able to contribute since I find the Relationship feature such a fertile area. Indeed, the difficulty pointed out with the specific spouse relationship (and relationships in general) is also an amazing opportunity! Since so many "dates" that we tend to create discrete attributes for are merely the Begin Date or the End Date of one or another Relationship. "Anniversary" is the begin date or a spousal relationship. "Birthdate" is the Begin Date of a Child Relationship (adding a "child" relationship to an "unknown contact" might be a more extensible way of tracking date of birth and age -- but for adopted individuals that doesn't perfectly track...). "Hire Date" is the Begin Date of an Employee relationship, Graduation is the "End Date" of a "student of" relationship. Indeed, even something as key to the CRM as "Address" is usually demarcated by the Begin Date and End Date of a "Custodial" relationship, "Student Housing" relationship(s), "Lessor - Lessee" relationship; "Roommate" relationship; "Ownership" relationship, "Spousal" relationship... for a child, college student, young adult, young adult, adult, and adult respectively (and typically). Indeed, our date of death is the end date (in this world) for a child relationship. It might be interesting to create some business rules and/or work-flow options to ask for tie-ins with these relationships in regards to other aspects of the data.

The two document links seem to be exactly what I was looking for and I am excited to find the time to delve into them.

Great Thanks!

(I trust you all understand that the lag time of response is the reality of my other non-civiCRM work impinging on this learning curve. Thanks for the responses!)

Brad

CiviCRM Community Forums (archive)

News:

Author Topic: ERD and Data Specs?... (Read 1223 times)

fedly

ERD and Data Specs?...

Yashodha Chaku

Re: ERD and Data Specs?...

xavier

Re: ERD and Data Specs?...

fedly

Re: ERD and Data Specs?...