email table structures in sugar5

[netron]

i'm trying to understand the emails table schema in sugar5 and its introduction of "bean_id"

for example, is the "bean_id" of email_addr_bean_rel the same as the bean_id of emails_beans - in other words
is emails_beans linked to email_addr_bean_rel via the bean id?

are there any documents/pointers on how the various email tables in sugar5 all link to each other?

[kuske]

Perhaps this post: http://www.sugarcrm.com/forums/showp...97&postcount=3 will help to understand the DB-tables for emails.

[netron]

great diagram -thanks... but in my sugar5a and sugar 5b installs there isn't any bean_module table.


mysql> show tables like "%bean%";
+----------------------------+
| Tables_in_sugar5a (%bean%) |
+----------------------------+
| email_addr_bean_rel |
| emails_beans |
+----------------------------+
2 rows in set (0.00 sec)

mysql> show tables like "%bean%";
+----------------------------+
| Tables_in_sugar5b (%bean%) |
+----------------------------+
| email_addr_bean_rel |
| emails_beans |
+----------------------------+
2 rows in set (0.00 sec)

[kuske]

The bean modules can be all modules like Accounts, Contacts, Opportunities,....
Bean is only a synonym for all these modules.

[netron]

so where does the id for the bean module come from?

( i'm writing my own script to migrate a sugar 4 database to a sugar 5 database... which is why i need to understand this new email schema)

is it one create_guid id for each module? so an id value for Leads, one for Accounts, another for Opportunities?

[netron]

just had a look at the example data for sugar5a. still not even sure why beans need to be in there for the purposes of normalising email address handling.

any idea why bean ids are in there , and why an emails_beans table exists?

[kuske]

The emails_beans table is the archive reference for emails.
If you select "import to Sugar" in Email view, you can select a relation to a bean where you want to archive this email.

[netron]

The emails_beans table is the archive reference for emails.
If you select "import to Sugar" in Email view, you can select a relation to a bean where you want to archive this email.

that doesnt really explain why there is a need for a "bean".

i can understand the need for data normalisation with regards to email addresses - but where do these "beans" come in?

what are they? and why is this extra complexity required?

7 tables just to handle email seems over the top to me.

still at a loss as to how to proceed with importing here. it seems to be needlessly complex.

[datasponge]

bean is only used as a generic word to mean any id from any table.

For example the accounts_contacts table relates accounts to contacts and contains the fields id, account_id and contact_id becuase this is a hard coded relationship. id is the unique id for an entry in that table and its primary key. account_id always relates to the accounts table id field. contact_id always relates to the contacts table unique id field.

But for emails you can relate an email address to any number of tables including accounts, contacts, leads, etc, etc., so rather than have a field for each possibly related table, you have one generic field that can be related to any table and it is generically referred to as a bean.

bean simply means any id in any table that uniquely identifies a record in that table.

With that definition, you can add an entry into email_addr_bean_rel to define a relationship for an email address to any other table saving that table's id that identifies a unique record in the bean_id field.

You can similarly relate a sent email to any other table using the emails_beans table.

Does that clarify or did I misunderstand your complaint?

Phil

[datasponge]

Also note that bean_module is not the name of a table, but a generic placeholder for whatever table you want to relate to email addresses.

For example the Contacts module uses the contacts table and the contacts table can have email addresses associated with contacts, so you should plug in the contacts table where bean_module is shown in the diagram. The bean_id in the email_addr_bean_rel table would be joined to the id field in the contacts table as the bean_id for contacts is the id field.

Most of the reason for the perceived complexity is because both acutal sent emails and email_addresses are being mapped and because they can be mapped to any table for any module, so you need a generic representation for this.

Phil