Optimise postgres database table design

Question

I am looking to optimise my database. The current design is one table company which is ID and a JSONB payload which can contain the type of company, address, leaders etc.

ID	Payload
6edf43d2-565b-4cad-9419-1bbb61441d7c	JSONB
fb6a649d-3aa6-42f0-a0f5-ea49b0e6dd33	JSONB

The JSONB payload looks something like this for a specific company:

{
  "type": "business",
  "leaders": [
    {
      "id": "01f6dcd0-02d4-11eb-b9cb-c7896e45862d",
      "name": {
        "title": "Mr",
        "lastName": "one",
        "firstName": "leader"
      },
      "contactMethods": [
        {
          "name": "email",
          "type": "email",
          "email": "leaderone@lead.com"
        },
        {
          "name": "landline",
          "type": "phone",
          "number": "234234234",
          "countryCode": 64
        }
      ]
    },
    {
      "id": "2bd9abe0-02d4-11eb-b9cb-c7896e45862d",
      "name": {
        "title": "Mrs",
        "lastName": "two",
        "firstName": "leader"
      },
      "contactMethods": [
        {
          "name": "email",
          "type": "email",
          "email": "leadertwo@lead.com"
        },
        {
          "name": "landline",
          "type": "phone",
          "number": "234234234",
          "countryCode": 64
        }
      ]
    },
    {
      "id": "35a09210-02d4-11eb-b9cb-c7896e45862d",
      "name": {
        "title": "Mrs",
        "lastName": "three",
        "firstName": "three"
      },
      "contactMethods": [
        {
          "name": "email",
          "type": "email",
          "email": "leaderthree@lead.com"
        },
        {
          "name": "landline",
          "type": "phone",
          "number": "234234234",
          "countryCode": 64
        },
        {
          "name": "mobile",
          "type": "phone",
          "number": "123",
          "countryCode": 64
        }
      ]
    },
  ],
  "addresses": [
    {
      "id": "01f6dcd1-02d4-11eb-b9cb-c7896e45862d",
      "type": "Australia",
      "country": "AU",
      "postcode": "2025"
    },
    {
      "id": "f6aa5550-2a15-11eb-8914-5fa8f55d3b03",
      "type": "NewZealand",
      "country": "NZ",
      "postcode": "239059",
      "streetName": "Martin Road",
      "streetNumber": "38A"
    }
  ],
  "createdAt": "2020-09-30T04:23:00.335780909Z",
  "legalName": "Company A",
  "updatedAt": "2021-05-27T06:16:37.733462415Z",
}

A company has many leaders and I am storing the contact method per leader. The company can also have many addresses. Now, the leaders keep chopping and changing and at the moment if I want to edit a leader I am needing to update the whole company payload, same with addresses. I am also trying to performance test my design by adding 10 leaders at at time however timeouts occur which I suspect is due to the same issue.

I am looking to re-design this structure optimally, I was thinking to have a separate table per entity (leader, address etc) which can reference back to the company table by an ID. However, in order to initially create a company it is mandatory to have at least one leader and one address so it doesn't seem to make sense to have a leader or address table to exist on its own.

What is the best performant design?

A "basically" or "essentially" or "in other words" that doesn't introduce or summarize a clear, precise & full description that you also give just means "unclearly" or "it is false that". PS [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/q/3653462/3404097) — philipxy, Aug 22 '22 at 07:14
What is your 1 specific researched non-duplicate question re how/why you are 1st stuck in what published presentation of what design method? [ask] [Help] [Why is “Can someone help me?” not an actual question?](https://meta.stackoverflow.com/q/284236/3404097) "optimally" & "performant" don't mean anything in particular. And after you have said what you mean, how are you stuck evaluating? [Why is “Can someone help me?” not an actual question?](https://meta.stackoverflow.com/q/284236/3404097) — philipxy, Aug 22 '22 at 08:25

score 0 · Answer 1 · answered Aug 22 '22 at 05:12

You are right, you should normalize the data model and avoid JSON in this case. If an address is missing in the data, simply set the corresponding foreign key to NULL to indicate that the company has no known address (yet). Complain to your data source about the lack of data quality :^)

Optimise postgres database table design

1 Answers1