Email Tables

Post questions on how to interpret your Bullhorn SQL Server backup of your Database.

Moderators: StaffingSupport, s.emmons

Post Reply
apsmith
Site Admin
Posts: 2051
Joined: Wed Dec 31, 1969 8:00 pm

Email Tables

Post by apsmith » Thu Feb 25, 2010 4:21 pm

Email Database Tables:

BH_UserMessage
----This is the main Email table. It contains one row for each Message.
----The contents are stored in the commentsCompressed column as a compressed binary
----The header information is stored in the smtpHeaderData column.
----The sendingUserID is populated with the userID of the sender, if present
----This table has a link, via the userMessageID column to the BH_MessageRecipient table

BH_MessageRecipient
----This table stores a row for each sender and recipient of the message.
----This has a pointer to the folder in which the email currently resides via the messageFolderID column
----If the message is permanently deleted, the messagefolderID will be null.
The system stores the last folder in the folder column in this format: Last Folder: 1234

BH_MessageFolder
----This table stores a row for each email folder.
----This is linked to the user via the userID column (points to any of the user tables (below))
----There is a link to itself if the folder has a parent folder: parentMessageFolderID
----There is a soft delete column: isDeleted

BH_UserMessageFile
----This table stores a row for each file attachment linked to an email.
----It will contain a name of the file: name.
----It will contain a link to the directory of the file: directory.
----The file will be stored in the directory location with a filename of the PK of this table.
For example, userMessageFileID of 1234, which is a .doc would be stored as 1234.doc.

BH_UserContact
----This table is the primary User table in the database. It stores a row for each candidate, client contact and internal user.
----For the purposes of this information, it is merely linked to by any userID FK.

BH_CorporateUser
----This is a child table, linked to BH_UserContact.
----It stores a row for just the internal users.
----An inner join to this table from BH_UserContact will allow for the returning of just internal users.

Schema Diagram:
Attachments
EmailTables.png
EmailTables.png (46.54 KiB) Viewed 12690 times
Andrew Smith
Director, Technical Operations
Developer Forum Admin

korrtec
User
Posts: 1
Joined: Fri Aug 03, 2012 10:01 am

Re: Email Tables

Post by korrtec » Thu Aug 30, 2012 6:02 am

How can I differ outbound and inbound messages?

apsmith
Site Admin
Posts: 2051
Joined: Wed Dec 31, 1969 8:00 pm

Re: Email Tables

Post by apsmith » Tue Sep 04, 2012 2:57 pm

korrtec,

Can you explain this in more detail? You should be able to determine this from the sender and recipient.
Andrew Smith
Director, Technical Operations
Developer Forum Admin

natael
User
Posts: 1
Joined: Mon Aug 15, 2016 6:08 pm

Re: Email Tables

Post by natael » Mon Aug 15, 2016 6:14 pm

Do you have any example code (SQL or otherwise) on decompressing the contents of the e-mail?

apsmith
Site Admin
Posts: 2051
Joined: Wed Dec 31, 1969 8:00 pm

Re: Email Tables

Post by apsmith » Tue Aug 16, 2016 9:41 am

There is an entire thread (or a few threads ) on this:

viewtopic.php?f=34&t=1672
Andrew Smith
Director, Technical Operations
Developer Forum Admin

Post Reply