Database schema for user custom field in SAAS ie. salesforce

kuyaz

Hi,

Anyone here know what kind of database engine & schema that salesforce use to facilitate multi-tenant SAAS and each tenant has its own custom field that they can define?

I read online, they use SQL (oracle) with EAV (Entity Attribute Value) for custom field schema.
Can anyone confirm this?

is EAV preferred compared to json native column that recently supported by mysql / maria in term of performance?
What is the down side for json column? Lack of join syntax?

Thanks

scottalanmiller

Are you trying to build your own CRM?

kuyaz

I got project related to above questions and want to decide the best practice for above case.

JaredBusch

@kuyaz said in Database schema for user custom field in SAAS ie. salesforce:

I got project related to above questions and want to decide the best practice for above case.

Nothing you are going to do would ever approach a scale that takes an entire division of a company to maintain.

Your logic here is totally backwards.

What are you trying to do?

kuyaz

@jaredbusch
I got project in CRM SAAS development.
So they want to add custom field feature like saleforce.

I am not trying to match their scale, just want to get some information about the database engine and structure, so we can at least stay under best practice guideline for such case.

The options I have currently :

EAV
JSON Column (Mysql / Maria recently supported this). / POSTGRESQL
NOSQL

Just not sure which is the best for my case above.

scottalanmiller

@kuyaz said in Database schema for user custom field in SAAS ie. salesforce:

@jaredbusch
I got project in CRM SAAS development.
So they want to add custom field feature like saleforce.

I am not trying to match their scale, just want to get some information about the database engine and structure, so we can at least stay under best practice guideline for such case.

The options I have currently :

EAV

JSON Column (Mysql / Maria recently supported this). / POSTGRESQL

NOSQL

Just not sure which is the best for my case above.

Why would Salesforce's decisions indicate a best practice or an appropriate practice for you? SF does a good job, don't get me wrong. But there are a lot of things to consider:

What SF is doing and what you are doing aren't related even if it is similar data types. So what is right for them at best gives you a starting point for you.
SF is old, really old. That's not to say that they've not kept up, but they are dealing with loads and loads of legacy code, data, and decisions that aren't relevant to you.
Just before SF made a decision, doesn't mean it's a good one. They might be living with some very bad decisions and just feel stuck with them or haven't evaluated other options.
SF's scale is one of the biggest things that dictates their decisions, so you can't look to them for guidenace if it isn't their scale that you want dictating what you perceive.
NoSQL is a massive pool of options, this doesn't exactly narrow things down.
This isn't a case where best practices exist. That's not something you can look for. You can, at best, look to see what others have used, what has come about since then, and attempt to extrapolate based on guesses as to their logic.