Schema for 3PL/development of a data warehouse

Is there a different database schema for the 3PL version of ShipHero? I would expect so, given how the program has to support different functionality.

We are trying to develop an internal data warehouse for reporting, fed by the pertinent data from ShipHero. As a SQL person, I am having some difficulties in not having a straight-forward schema to look at and link between tables/data sources. Also, most of the help topics I see don’t deal with the added elements that exist for 3PL.

Any help, guidance, direction, etc. is greatly appreciated. And if there are any providers that have written this already, or have the expertise to do so, I would like to hear from you too. I don’t want to re-invent the wheel

Hi @JCox
The schema should be exactly the same as for a regular account, the only difference is that you will need to include a field called customer_account_id that will tell for which customer you will be performing the Query/Mutation for.

For example, if you wish to create an order, and your Bearer is associated with the 3PL account you will have to do the following

mutation {
  order_create(
    data: {
      order_number: "AllocationTesting"
      customer_account_id:"XXXX"
      …
 }
  ) {
    request_id
    complexity
}
}

Because the order should get created on the 3PL’s Client account.
The same happens for Products for example.

Let me know if this is now what you were looking for.
Thanks in advance!
Tom

Tom,

Thank you for the reply and feedback. Based on your reply, it would appear to me that the only difference between the 3PL and non-3PL versions is the customer_account_id field. Is that correct? Is this field used across all of the tables?

Jeff

That is correct @JCox
It will depend on the action you will be performing, but for most of them, you will need to use that.

The reason is that both Products and Orders are associated with the customers (and not the 3PL), but on the other side, Inventory has to be managed on the 3PL account instead because the 3PL is in charge of managing the warehouses.

So if you create an order or a product, then you will need to create those in the customer account (using the customer_account_id)
But if you want to manage inventory, you would just need to use the product id and warehouse id of the 3PL.

Let me know if you need more specific examples for this
Thanks again!
Tom