Full Database Schema's for Salesforce - Oralce and MySQL

mike · #1 07-09-2007, 11:03 AM

Well let's continue with the database schema's so you can get more ideas of what the structures look like. I am releasing the full MySQL and Oracle database schema's in zip files that are attached to this article

One problem that needs to be addressed is that the lengths of oracle columns cannot exceed 30 bytes / 30 characters in length. In some cases on the tables you have to make adjustments on some of the column names in order for Oracle to accept the column names.

An example of a table which needs to have this addressed is the Salesforce.com Profile object / table. If you were to try and create the Profile table with the same field names that come straight from Salesforce, you would receive errors. Let's look at this example:

Code:

CREATE TABLE sforce_profile(

Id VARCHAR2 (18),
Name VARCHAR2 (765),
PermissionsEditTask CHAR (5),
PermissionsEditEvent CHAR (5),
PermissionsManageUsers CHAR (5),
PermissionsModifyAllData CHAR (5),
PermissionsCustomizeApplication CHAR (5),
……

The field PermissionsCustomizeApplication would cause an error due to the length being over 30 characters (In Oracle Only). If you do a simple modification to the name of the column such as changing it to:

Code:

PermCustomizeApplication CHAR (5),

See what I did, I removed the missions off of Permissions, and then you will not have any issues. It just makes things that much easier when trying to create tables for your local copy of data. So in the case of the Profile object your table would look something like this:

Code:

CREATE TABLE sforce_profile(

        Id VARCHAR2 (18),
        Name VARCHAR2 (765),
        PermEditTask CHAR (5),
        PermEditEvent CHAR (5),
        PermManageUsers CHAR (5),
        PermModifyAllData CHAR (5),
        PermManageCases CHAR (5),
        PermEditForecast CHAR (5),
        PermManageSolutions CHAR (5),
        PermCustomizeApplication CHAR (5),
        PermEditReadonlyFields CHAR (5),
        PermRunReports CHAR (5),
        PermViewSetup CHAR (5),
        PermTransferAnyEntity CHAR (5),
        PermManageSelfService CHAR (5),
        PermManageCssUsers CHAR (5),
        PermImportLeads CHAR (5),
        PermManageLeads CHAR (5),
        PermTransferAnyLead CHAR (5),
        PermViewAllData CHAR (5),
        PermEditPublicDocuments CHAR (5),
        PermManageDashboards CHAR (5),
        PermSendSitRequests CHAR (5),
        PermApiUserOnly CHAR (5),
        PermDisableNotifications CHAR (5),
        PermManageCategories CHAR (5),
        PermConvertLeads CHAR (5),
        PermPasswordNeverExpires CHAR (5),
        PermUseTeamReassignWizards CHAR (5),
        PermEditActivatedOrders CHAR (5),
        PermInstallMultiforce CHAR (5),
        PermPublishMultiforce CHAR (5),
        PermEditOppLineItemUnitPrice CHAR (5),
        PermManageTerritories CHAR (5),
        PermCreateMultiforce CHAR (5),
        PermSolutionImport CHAR (5),
        PermManageCallCenters CHAR (5),
        PermEditReports CHAR (5),
        CreatedDate DATE,
        CreatedById VARCHAR2 (18),
        LastModifiedDate DATE,
        LastModifiedById VARCHAR2 (18),
        SystemModstamp DATE,
        Description VARCHAR2 (765)
    );

So in closing you can view the two attachments to this article. They are full working schema's for Oracle and MySQL. Please keep in mind that these schema's were built off of a developers account. You may have custom fields that you will need to define and they end with "__c". If you have any questions please do not hesitate to contact me

~Mike

mike · #2 07-10-2007, 12:29 PM

One thing that you can do to MySQL is change the data type on Salesforce DATE fields, example:

Account Object:
CreatedDate column/field. I orginally setup this field as a DATE type, but you can change it to DATETIME. When you change the type, you will see a difference in your data in MySQL:

Code:

DATE type:
2007-07-10
`createddate` date default NULL,

Code:

DATETIME type:
2007-07-10 12:49:40
`createddate` datetime default NULL,

You can do this to any date field in MySQL