Users, Businesses, Orders, Locations, Oh My!

[Andrew_W]

So I’m trying to structure out my schema_version 1 and I wanted to run it by everyone to let me know if I’m on the right track or way off. I know querying is a big thing so I’ll quickly break down relationships and how the application would be used, then my current schema, and then considerations for a schema_version 2. With that said here’s the app flow:

A User can can add a Business which can have several BusinessLocations which can have many employees and many orders.
An order will have one customer, one recipient, be created by an employee, and have providers.

An employee is a user that has certain role for permissions and belongs to a business location.

Here are my current collections: (Assume all documents for each collection have a “_id” by default)

user: {}
business: {
    owner: user_id,
    locations: [ businessLocation_id, ... ]
}
businessLocation: {
    businessId,
    employees: [employee_id, ...]
    address: {},
    ...
}
employee: {
    businessLocation_id,
    user_id,
    role
}
order: {
    businessLocation_id,
    employee_id,
    *providers: [],
    customer: {},
    recipient: {}
}

*Do I need providers in their own document or can I get away with an embedded array of objects that are only to that order if I know that there is a limit per order and I don’t need to know if the provider is part of other orders?

Indexes - Missing any? Wrong ones?

• employee_orders (from order document)
• business_employees (from employees document)

Links/Refs - Am I missing any? Are any unnecessary?

Thinking ahead, I will eventually want to create flexibility for enterprise; An enterprise (organization) may have many brands which could own many businesses with many locations. But this isn’t v1, just something to keep in mind when I eventually may need to migrate data.

Thank you SO much! I know this is a doozy!

[Pavel_Duchovny]

Hi @Andrew_W,

The main approach I take into account when I start building a schema is first I would think of the critical paths of application query/crud will work and what is the relationship magnetuted between the entities as well as if there is a justification of each to be stored as a document of its own.

The following blogs are very useful to start.
https://www.mongodb.com/article/mongodb-schema-design-best-practices

https://www.mongodb.com/article/schema-design-anti-pattern-summary

Performance Best Practices: Indexing | MongoDB Blog

Now lets break down your needs and enteties:

• User (Employees are also users with certain characteristics)
• Business (+Business Location)
• Orders (1 Customer , 1 Recipient)
• Provoders

A User can can add a Business which can have several BusinessLocations which can have many employees and many orders .
An order will have one customer , one recipient , be created by an employee, and have providers .

Ok based on the above I see the following potential schema:

Users Collection
{
UserId,
EmployeeInfo : {
EmployeeId,
BusinessId,
Roles : [],
Permissions :[]
}
MoreUserDetails,
...
}

Businesses Collection
{
BusinessId,
BusinessesLocations : [{addresses}]
...
}

Orders Collection
{
 OrderId ,
Business : { BusinessId, CurrentAdress},
UserId/EmployeeId,
providers: [providerId],
    customer: {},
    recipient: {}
...
}

Providers Collection
{
ProviderId,
ProviderDetails 
...
}

The main Idea is that a user is a main entity which with some additional info considered an employee in some application views. A business have addresses but there is not justification of having locations without a business object. Orders have both pointers the business and the user/employee that created it.

Providers can be referred via ids as I believe their ammount is eventually limited per order.

In terms of indexes this is what I thought, but its dependent on your specific queries:
Users - {userId }, {BusinessId, EmployeeId}, {EmployeeId}, {Roles?}
Businesses - {BusinessId}, {location geo index?}
Orders - {OrderId}, {Business.BusinessId, Business.EmployeeId}, {Providers?}
Providers - ProviderId

Let me know if that makes sense.

Thanks
Pavel

[Andrew_W]

Thank you @Pavel_Duchovny! I’ll take some time to digest all this and get back to you.