Author Topic: Data Import / ER Diagram? (Read 1956 times)

jw127 · April 18, 2011, 02:20:31 pm

I'm trying to figure out how to best import our Access data to CiviCRM. I've gone to a two-day seminar, read the Floss Manual, and just spent at least an hour browsing online documentation and the forums - nowhere yet have I seen/heard/read this explained in clear detail. How do I find the most recent ER diagrams (3.4 and 4.x)??

The most recent info I could find is this bit added June 23, 2010: http://wiki.civicrm.org/confluence/display/CRMDOC33/CiviCRM+data+architecture I'm assuming the newer versions have an updated schema. In the warning message on that page it says, "Consult your version's database schema for an up to date list of fields." I'm assuming that means call it up through mysql ... but then I'd want a version I can print out. I'm assuming I'm not the first one to need this?

Suggestion: maybe this should be in the Database Planning and/or Import section of the documentation.
Sidenote: I'm sure I'm not the first to mention this, but the documentation search box doesn't work too well. Searching for "er diagram" yields this:
Did you mean: ancestoring:35127456 AND er diagram
No results found for ancestorIds:35127456 AND er diagram. Please try...

Thanks

Erik Hommel · April 18, 2011, 11:27:01 pm

There are some diagrams in the documentation:
http://wiki.civicrm.org/confluence/display/CRMDOC33/CiviCRM+ERD+3.1. The most recent ones (as in 3.4 /4.x) are probably not there yet. As far as I know these ERD's are community created, so if you think it is vital to have them you are cordially invited to contribute

What specific issues do you run into when making decisions about your Access data? I can imagine some of them could be solved by knowing the full data dictionary (one can always have a look at the CiviCRM xml-schema?) but for some decisions you might also have to know a little more about the CiviCRM functionality. If you come up with some specific questions we might be able to help you?
Erik

xcf33 · April 19, 2011, 06:29:26 am

I would personally be hesitant to directly port data from access directly to a mysql CiviCRM database just because the Civi database is pretty highly normalized and have a lot of constraints, etc.

If the data you are trying to migrate doesn't contain complex relationship or if you don't have a very large dataset I think there are a number of options out there (CSV) based that can make the task much easier.

Cheers!

jw127 · April 20, 2011, 10:54:35 pm

Thanks for both of your replies. I asked for an ERD because that's what our Access guy asked for, but I'm not sure it's what we need. Erik mentioned the xml-schema/full data-dictionary, which might be all we need, but how would I find those? Searched high and low. Are those similar to the Data Model? I think the Data Model would suffice. I found a link on the web archive to an old 1.0 data model, but have yet to find anything like it for newer versions: http://classic-web.archive.org/web/20070402090740/http://wiki.civicrm.org/confluence/display/CRM/Data+Model#DataModel-dmRef I understand the idea of contributing to the community, and I would love to eventually, but there's not much to contribute when I'm still trying to figure it out. I'm guessing/hoping this already exists somewhere and I just can't find it. If this doesn't exist, how do other people go about planning how to match fields and tables to the existing CiviCRM architecture? Would I have to go directly into the mysql tables and look at them one by one?

Here's a quick (simple) example of why I want to see a Data Model...
Our current database has groups called "email preferred", "do not mail", etc. Obviously we weren't using "groups" in the same way as Civi does. I'm assuming there's a table called "Communication Preferences" that matches up with the contact edit screen, so I need to re-organize our data accordingly before importing. But it makes it tough to figure out how to organize our data not knowing the exact constraints, etc. Again I realize I could look at the mysql from command line, but is that really the best way? It just seems I'm missing something obvious here. Thanks again for the help.

Erik Hommel · April 20, 2011, 11:46:37 pm

The xml schema you will get if you install from svn, it is the schema from which the data access layer of CiviCRM (the DAO's) are generated. You can have a look at this link:
http://wiki.civicrm.org/confluence/display/CRMDOC33/Installing+CiviCRM+from+subversion+%28SVN%29+repository but perhaps it is a bit too heavy for where you are now.
If you get the MySQL Workbench from http://dev.mysql.com/downloads/workbench/5.2.html, you can have a look at all the tables in the database and create ERD's. That is better than the mysql command line

. You are not missing something obvious here, this is how I build up the knowledge: look in the database and ask questions on the forum. There is no more up to date diagrams than mentioned in my earlier post. I agree with you it would be very useful to have a full data dictionary or set of diagrams, but it is not present. Do ask your specific questions here on the forum or on IRC!

The communication preferences you are looking for are in the table civicrm_contact. If you have a contact that was part of your "do not email" group, you would have to set the value of the field 'do_not_email' to 1 in the record in civicrm_contact for that contact. How where you envisaging migrating your data? Are you going to develop a conversion script?

Eileen · April 21, 2011, 12:40:36 am

Data imports are a piece of string. Generally I would recommend that if your data is too complicated to import using the built in import tools & you are not familiar with CiviCRM you probably want to pay someone who has done it before to do it.

Having said that you can get a long way using the front end import tools - try doing an export of contacts from Civi - the exported CSV will give you an idea of the layout to import back in using a csv.

If that isn't going to work for you you need a method focussed around a script that uses the api. You can explore the api fields @ http://sandbox.civicrm.org/civicrm/ajax/doc#explorer. Or - you can use Xavier's csv import to api method http://civicrm.org/blogs/xavier/api_batch_tools.

Personally I use something different again http://civicrm.org/blogs/eileen/civimigrate-–-how-i-got-on…-incl-pledge-imports but it's a learning curve

CiviCRM Community Forums (archive)

News:

Author Topic: Data Import / ER Diagram? (Read 1956 times)

jw127

Data Import / ER Diagram?

Erik Hommel

Re: Data Import / ER Diagram?

xcf33

Re: Data Import / ER Diagram?

jw127

Re: Data Import / ER Diagram?

Erik Hommel

Re: Data Import / ER Diagram?

Eileen

Re: Data Import / ER Diagram?