Understanding the NetSuite Database and SQL

NetSuite for Administrators

NetSuite at its core is actually just a glorified database with a user-friendly interface managed through SQL. Do you find NetSuite sometimes difficult to fully understand? Knowing the roots and foundation of NetSuite will give you super-powers in understanding, debugging, and using NetSuite!

Databases

You may not have realized this before, but NetSuite for the most part is really just a database covered with a usable interface. It is certainly possible to use NetSuite without understanding the intricacies of a database, but knowing some of the basics may actually transform how you view and use NetSuite.

What Is a Database?

A database is simply an ingenious way to store large amounts of data digitally. In 1979, Oracle (the company behind NetSuite) created a database that would propel the company to what it is today and make it the owner of the most popular database for over 40 years. We would expect that the creator of such a database would also be able to use it in NetSuite in an effective way. The NetSuite database is what is called a relational database. You can read more about databases from Oracle here.

A relational database, in simple terms, is composed of what you can think of as individual tables or mini Excel sheets that are linked together. See the following example:


Item Record

id item name location
item1 “Test Item 1” loc1
item2 “Test Item 2” loc2
item3 “Test Item 3” loc3

Location Record

id location
loc1 “Seattle, WA”
loc2 “Denver, CO”
loc3 “Boston, MA”

In this fictitious example above, we have two “tables” in a Relational Database. The first one (an item record) lists some information relevant to the item. But notice the location column, it just gives reference number, linking to the ID of the location record. And the Location record may have similar references to other tables in the database. This creates a chain across all the relevant tables. This chaining allows data to be stored and accessed very efficiently.

Already, you may be beginning to see the connections to NetSuite. Databases store the information, but how do we access the information? This is where the genius of SQL and NetSuite’s user interface comes in.

SQL

SQL (“Structured Query Language”) and NetSuite’s customized “SuiteQL” retrieve information from the NetSuite database. It allows for all kinds of manipulation of the data to connect tables and combine information. When you load a list of items in your NetSuite account, NetSuite behind the scenes is actually running a search (and more specifically, a SQL search of the NetSuite database) to find your list of items.

And things really begin to make a lot of sense when we look at a Saved Search in NetSuite. The “joins” are using the “hyperlinks” within each table to find the related table with our information. Sorting, grouping, and essentially every other part of a Saved Search is simply describing what SQL does. Does this excite you as much as it excites me?

And what’s even more powerful is that NetSuite allows us to use “formulas” in Saved Searches. We can actually use SQL in its native form to find and logically handle information in ways that aren’t supported in NetSuite’s user interface (see this post for an example). Some powerful things can be done here!

Conclusion

NetSuite, with all its complications and intricacies, is at its core very simple. Understanding the roots and simplicity of NetSuite can help us overcome complex obstacles and give us superpowers in using, debugging, and enjoying NetSuite. Thanks for reading!