Name Your Columns After X12

There are only two hard things in Computer Science: cache invalidation and naming things.

– Phil Karlton

I have good news if you’re building a custom software solution in logistics, retail, manufacturing or any of the host of other industries that X12 maintains documents for: if you’re struggling to name something, don’t worry, X12 already named it.

In this post, I’ll explain just how helpful that can be, why you should strongly consider using X12 naming schemes for your database fields, when to store X12 codes when you’re ingesting EDI, and when you should stray from this naming scheme.

Let’s assume we’re working in logistics and we have a stop entity in our system, which belongs to a shipment. Our goal is to ingest an inbound 204 load tender and use the data contained therein to populate our shipments table with a single entry (which I will not discuss) and its associated stops table with as many entries as there are stops.

The Source Data For Stops

In the 204, stop data is contained in the S5 loop, which starts with (and thus always contains) the S5 segment and usually contains several other segments which describe the location of the stop, when you’re supposed to be there, whether you’re loading or unloading, who the contact person is, and so on.

The EDI for that might look like the following:

S5*1*LD*34739.53*L*0*PC*1041.031*E
G62*69*20241215*U*2359*ET
N1*SF*ACME NORTHEAST RDC*93*2361
N3*2200 INDUSTRIAL BLVD
N4*ALLENTOWN*PA*18103*USA
G61*SH*J. SMITH*TE*610-555-0142

If we translate that EDI into JSON, it gets more readable:

{
  "stop_off_details_S5": {
    "stop_sequence_number_01": 1,
    "stop_reason_code_02": "load_LD",
    "weight_03": 34739.53,
    "weight_unit_code_04": "pounds_L",
    "number_of_units_shipped_05": 0.0,
    "unit_or_basis_for_measurement_code_06": "piece_PC",
    "volume_07": 1041.031,
    "volume_unit_qualifier_08": "cubic_feet_E"
  },
  "date_time_G62": [
    {
      "date_qualifier_01": "scheduled_pick_up_date_69",
      "date_02": "20241215",
      "time_qualifier_03": "scheduled_pick_up_time_U",
      "time_04": "2359",
      "time_code_05": "eastern_time_ET"
    }
  ],
  "name_N1_loop": [
    {
      "name_N1": {
        "entity_identifier_code_01": "ship_from_SF",
        "name_02": "ACME NORTHEAST RDC",
        "identification_code_qualifier_03": "code_assigned_by_the_organization_originating_the_transaction_set_93",
        "identification_code_04": "2361"
      },
      "address_information_N3": [
        {
          "address_information_01": "2200 INDUSTRIAL BLVD"
        }
      ],
      "geographic_location_N4": {
        "city_name_01": "ALLENTOWN",
        "state_or_province_code_02": "PA",
        "postal_code_03": "18103",
        "country_code_04": "USA"
      },
      "contact_G61": [
        {
          "contact_function_code_01": "shipper_contact_SH",
          "name_02": "J. SMITH",
          "communication_number_qualifier_03": "telephone_TE",
          "communication_number_04": "610-555-0142"
        }
      ]
    }
  ]
}

Which Field Names To Copy Outright, And Why

Let’s start with the why: when the names of your fields match the names of X12’s fields, you get some important benefits.

First, you eliminate a whole class of annoying internal assignments, e.g. stop.title = x12_data.name, in favour of mass assignment or field iteration. In Ruby, that might look something like this:

ADDRESS_FIELDS = %w[state_or_province_code postal_code country_code].freeze

n4 = s5_loop["name_N1_loop"].first["geographic_location_N4"]
attrs = n4.transform_keys { |key| key.sub(/_\d\d\z/, "") }

stop.assign_attributes(attrs.slice(*ADDRESS_FIELDS))

The only work you’re doing is stripping the element position off the end of the key. Everything else is a lookup by a name someone else thought up.

Second, the same is true when you’re sending outbound data, e.g. a 214 shipment status update. The 214 usually includes the MS1 segment, which wants State or Province Code as one of its fields. If you named your field stop.stateOrProvinceCode instead of just state (or worse, region), it’s very easy to put this together.

Third, you reduce the cognitive load of mentally translating between names in your system and names in the X12.

When To Store X12 Codes

It’s often a good idea to store the X12 code as-is.

First, it’s often just as readable as values you’d make up yourself. LD meaning “load” and UL meaning “unload” are sufficiently clear when they’re the values in a column named reason_code.

Second, you often need to send the data back. The values in the S5 loop can reappear on an outbound 214 or 210, and the partner who receives these documents expects the codes to match.

Third, converting it might entail data loss. The X12 time code LT means “local time”. “Local time” is not a timezone. Absent a location, it doesn’t actually mean anything. If you convert it to an arbitrary timezone when you ingest it (e.g. by treating it like UTC), congratulations, you just destroyed it.

This leaves you with two options: save the code as-is (definitely do this), and/or compute a resolved timezone using the combination of the date and the stop’s location, which requires geocoding (this is optional and depends on what you need in your system).

When To Stray From This Naming Scheme

There are several valid reasons to depart from this naming scheme, so long as you accept that every departure costs you one hand-written assignment (stop.city = x12_data.city_name) forever.

First, sometimes you need to disambiguate a column name because X12 uses the same name more than once in different segments. For example, the N1 segment has a name element and so does the G61 contact segment.

X12 can use name twice because the segment tells you which is which. A table row has no segments, so you might choose name and contact_name.

A similar situation crops up with the G62 date segment, which can repeat, e.g. when one instance carries the earliest arrival date and another the latest. Both are dates, but you’ll need fields that differ in your table: e.g. start_date and end_date. This means the G62-01 qualifier value became part of the column name.

Second, you might want to trim words that are in the table name already, because the alternative just…looks gross in code. The S5 segment’s elements are stop_sequence_number and stop_reason_code. If you didn’t rename these, you’d end up with stop.stop_sequence_number. 🤮 That said, it’s still a tradeoff, and may not be worth making, especially since only coding agents look at your code these days anyways, and they never feel nauseous.

Third, you can rename something because damnit, you’re making the decisions around here. In other words, these aren’t rigid rules, they’re just guidelines, intended to help you build systems that are maintainable and easy to understand.

Just remember, before you decide not to use the name X12 uses: deciding what to name things is probably half their job, and they’ve been doing it for four or five decades. So when in doubt, call it state_or_province_code and move on.