Salesfoce Database Schema Setup Tutorial One - The opportunity object

mike · #1 06-27-2007, 09:27 AM

When I started working with Salesforce.com and setting up these database replication jobs, I had no idea where to start on the database structure. Please understand that I am not a DBA by any means and would never begin to understand a job such as that. I had no idea what the data types meant when I read the API documentation and not one single clue on how I was going to setup a local database structure from our instance of salesforce.

At first I would have thought, and still do to this day, that salesforce should be leading the way in this and possibly have some sort of database scheme for the standard objects/tables that are used in Sforce. I searched all over the developer network or other sites for such things as tutorials or example schema's or an idea on where to start. Not one of the people from developer's forum at Sfroce would help or even hinted on what I needed to do. I like to help people (to a certain point) and starting from this entry I am going to be showing some examples of database schemas for both Oracle and MySQL.

The schema examples will come from my developer account. So let's get to it! I will start with the Opportunity object and post two snippets in replies to these threads and then I will attach a zip file that will have two files in it. Each schema has been tested in both MySQL and Oracle 10g, both table creations will work.

I will be doing most of the main objects/tables and I hope this helps the community

If anyone has any comments or questions please use the contact form on this site or join the community and let me know your thoughts

Mike
6/27/07

mike · #2 06-27-2007, 09:29 AM

Here is the MySQL Snippet:

Code:

-- Host: localhost
-- Generation Time: Jun 27, 2007 at 08:52 AM
-- Server version: 5.0.18
-- PHP Version: 5.1.2

-- 
-- Table structure for table `opportunity`
-- 

CREATE TABLE `opportunity` (
  `Id` varchar(18) NOT NULL default '',
  `AccountId` varchar(18) default NULL,
  `IsPrivate` char(5) default NULL,
  
.....

mike · #3 06-27-2007, 09:30 AM

Here is the Oracle Snippet:

Code:

/************************************************
    TABLE FROM SALESFORCE.COM OBJECT Opportunity   
    Date: 6/27/2007
*************************************************/

    CREATE TABLE Opportunity(

        Id VARCHAR2 (18) primary key,
        AccountId VARCHAR2 (18),
        IsPrivate CHAR (5),
        Name VARCHAR2 (360),
        Description CLOB,
        StageName VARCHAR2
......

mike · #4 06-28-2007, 08:41 AM

I decided that making it mandatory for people to register to view the tutorials, files, or other items on this small site was not important. If people want to join to discuss salesforce tutorials, questions about using PHP and Salesforce's API then that is completely up to you

mike · #5 07-02-2007, 10:04 AM

Attached are working copies for both oracle and mysql of the account object / table that can be used for local schema setups for salesforce data replication.

Please contact me if you have any questions, comments, or concerns

~Mike

#6 07-18-2007, 09:04 AM

Hi,

There is no "standard" salesforce.com schema to publish, as the schema changes depending on your login credentials and how your administrator has setup your system.
In the view which you supply, there are a few fields which end with "__c", e.g. TrackingNumber__c. These are custom fields which are available to your salesforce.com login because your admin has created them
These fields are not available to other salesforce.com users, either because they are in a different "org" or because field level security dictates that they do no have privilege to see them.

Other fields may be exposed depending on which features have been enabled for your org, e.g. orgs which have multi-currency support will have an field called IsoCurrencyCode for every currency field. Orgs which have self-service enabled will have extra fields on contact records.

As well as this field level security, your administrator can restrict access to entire objects, so the opportunity "table" may be visible to sales users, but not support users.

mike · #7 07-20-2007, 09:33 AM

Quote:

Originally Posted by Unregistered

Hi,

There is no "standard" salesforce.com schema to publish, as the schema changes depending on your login credentials and how your administrator has setup your system.
In the view which you supply, there are a few fields which end with "__c", e.g. TrackingNumber__c. These are custom fields which are available to your salesforce.com login because your admin has created them
These fields are not available to other salesforce.com users, either because they are in a different "org" or because field level security dictates that they do no have privilege to see them.

Other fields may be exposed depending on which features have been enabled for your org, e.g. orgs which have multi-currency support will have an field called IsoCurrencyCode for every currency field. Orgs which have self-service enabled will have extra fields on contact records.

As well as this field level security, your administrator can restrict access to entire objects, so the opportunity "table" may be visible to sales users, but not support users.

This is true and I totally agree with your statement. However I stated that these layouts and schema's were built on a developers account. If you register for a developer's account then those custom fields are created for you when your developers account is built.

I did not change any of the fields that are created, custom or standard on any of these and have stated that these are just examples to give developers ideas on what they can build via using the php toolkit and different layouts.

Again these schema's and PHP examples/scripts are just examples against a developers account and are not meant to work out of the box with anyone's particular instance/org of salesforce

It's more like a mashup! I hope that helps and clarifies any statement that you posted

Thanks,
Mike

mike · #8 07-20-2007, 03:34 PM

Also wanted to add that I am not only a developer for my Organization's instance, but also I have administrative rights to our instance.

I just took it as that most developers would have administrative access to salesforce so they can add and remove tables, fields data extractions, and to basically work on all aspects of salesforce.

All the tutorials on this site are meant to work as a whole for an organization, not an individual's needs!

#9 08-17-2007, 01:43 AM

Please help me to know what all are the tables in Salesforce.com and what is the relationship among those tables.

mike · #10 08-22-2007, 08:05 PM

Kumar sorry I have been on vacation in Hawaii for the past 12 days. Are you wanting to know the name of each table/object ? I mean that is question that can be answered in many ways! Each organizations instance can be different if they have custom objects.

I will post a script in a few days that will print out a list of your organizations list of tables, will that work?

~Mike