Background
Our catalog data (items, channels, brand hierarchy) had fragmented across a couple of deprecated internal tools and a pile of team-owned mapping spreadsheets. Everyone had their own "source of truth," which in practice meant nobody did. I owned the project to fix that: take a clean-room entity-relationship diagram for what the data should look like, and make it real inside NetSuite.
That second part is where it got interesting. An ERD assumes you can define arbitrary entities, foreign keys, and join tables. NetSuite gives you a fixed set of native records (Item, Customer, Department, and so on), custom fields you can bolt onto them, and custom records you can define yourself. No CREATE TABLE, no real foreign key constraints, no free many-to-many joins. Whatever the ERD says, you're modeling it with the primitives NetSuite actually gives you.
The shape of the data
The ERD split into two pieces:
A product classification chain (every Item rolls up through Type → Group → Category → Brand), and a channel side where the same Item can be listed on several sales channels, each with its own status and ship-from location. One is a strict hierarchy, the other is many-to-many. NetSuite doesn't treat those the same way, so I didn't either.
The hierarchy: custom records, not custom lists
The lazy option for Brand, Category, Group, Type is a native Custom List: a flat dropdown. Fine for a demo, and it falls apart the moment a Type needs to know its Group, or a Category needs a status field of its own.
I built each level as its own custom record instead, chained by list/record fields:
| Entity | NetSuite object | Links to |
|---|---|---|
| Brand | customrecord_brand | N/A |
| Category | customrecord_category | Brand |
| Group | customrecord_group | Category |
| Type | customrecord_type | Group |
| Item | native Item, via custitem_item_type_ref | Type |
More records to maintain than a list, but they can carry a status field, get referenced from other records, and stop someone from retyping "Runing Shoe" into a text box for the third time.
The field that isn't a foreign key, on purpose
The original ERD had an is_parent boolean and a real parent row other SKUs would point to. In the actual business, a parent SKU is just a label ("these five SKUs are the same product in different pack sizes"), not a thing anyone buys or ships.
Modeling that as a real linked record means creating a non-sellable Item just to have something to point at, and an Item in NetSuite drags in accounting periods, inventory, purchasing history. Every Item is implicitly something the rest of the system can transact against. A fake "parent" item to satisfy a foreign key wasn't going to stay contained to one field. It would show up in every downstream report that touches Item.
What we used instead is custitem_parent_sku, a free-form indexed text field on Item. Any SKUs sharing the same string are treated as siblings. No referential integrity, and the SOP has to compensate for that at SKU-creation time instead of the database enforcing it for free. It's also exactly the pattern NetSuite already uses for ASIN, so the business team wasn't learning something new, and it doesn't require inventing products that don't exist in a system that assumes every product does.
Junction records: you build your own many-to-many
An Item sold on three channels needs three rows linking it to each one, which is the textbook many-to-many problem. NetSuite doesn't ship a join-table primitive, so you make one: a custom record (customrecord_item_channel_listing) with three list/record fields pointing back to Item, Channel Listing, and Fulfillment Node.
On its own that record is not something you'd hand a business user and expect them to find useful. So it gets surfaced as a sublist on a new "Channel Management" subtab on the Item record, one row per channel the item sells on, sitting next to purchasing and inventory info where people already look. The junction record does the relational work. Nobody using it has to know it's there.
What I left out
The ERD had created_date and last_updated_at on every table. I dropped both from every custom record, because NetSuite already stamps Date Created and Last Modified on everything for free. Re-adding them as custom fields would just be two more columns to keep in sync with data the platform already tracks. Small thing, but worth checking before you model a field yourself: does the platform already give you this.
A schema isn't done until someone owns each field
The implementation plan doesn't stop at field definitions. Every field also gets a data source, a field owner, and a maintenance flow:
This is the part I'd have skipped if I were treating it as a pure modeling exercise. A field with a clean definition and no clear owner drifts right back into the same fragmented mess the project was supposed to fix. Writing down who updates a field, and when, mattered about as much as the ERD did.
Archaeology
Some of what the ERD called for had already existed once, in a tool that got deprecated in a company-wide tech stack change and quietly stopped being maintained after that. Before rebuilding those pieces, I went and read through the old tool's archived source to see how it had modeled the same relationships the first time around. Some of that logic was worth keeping. Some of it explained why the data was already inconsistent in the first place. Either way it was faster than re-deriving decisions that had already been made once.
Where it landed
The classification hierarchy, the channel junction, and the custom fields on Item all shipped to production. Item and channel data that three or four teams had each been maintaining their own copy of now has one validated source in NetSuite, with an owner documented for every field.
If there's one thing I'd tell myself before starting, it's that an ERP is not a relational database with a UI on top, and the fastest way to get stuck is assuming it is. The free-form text field, the junction-record-as-sublist, dropping the redundant timestamp columns: none of those were the "textbook correct" answer. They were the answer that worked inside NetSuite's actual constraints, which is a different question than the one the ERD was answering.