There are many concepts that are brushed aside as extremely basic.  This post is going to be for someone that does not know anything about SQL and may seem overly basic to anyone that already knows about databases.  This is the first post in a series and will address database structure.

After speaking with several people at a SQL Saturday that was not DBA’s, they wanted to learn the basics of SQL.  However, the session we spoke and much of the feedback was that it focused too much of the vocabulary and not enough on the structure.  Most of the frustration came from not knowing how to write the format to utilize the vocabulary.

I found that most classes and tutorials focus on the language online.  This is because SQL is a standardized language and has many different interfaces that can access the data.  Some examples of other interfaces are Cubrid, Firebird SQL, MySQL, SQL Server, etc.

First, let us discuss what a SQL database essentially is.  I will be breaking this down to the basics and if you know about databases it may seem overly simplified.  Databases are a storage of data and a way to organize data in a meaningful way.  A relational database is where data is stored between tables that relate to each other.  This starts with a database that will hold the various tables.  In each table, there are rows and columns where the data sits.  After the data is in a database, there needs to be a way to search for relevant information.  SQL, which stands for Structured Query Language, was designed as a standard format to pull data from a database.

This post is going to discuss databases, tables, columns, and rows. For the purpose of this post, I will be utilizing Microsoft Dynamic GP 2015 and SQL Server 2014.

Databases

With a basic install of Microsoft Dynamic GP with no custom databases and only Fabrikam created you will see the System Databases, DYNAMICS, and TWO.

The system databases are the default databases that control the SQL instance you are on.  They are master, model, msdb, and tempdb.

DYNAMICS would be the overall database for Microsoft Dynamic GP.  When installed DYNAMICS can be changed to another name.  In a multi-tenant environment, you can have multiple DYNAMIC databases on an instance as long as they have different names.  For the sake of simplicity, we will not be altering the name.

TWO is the Fabrikam database that is provided by Microsoft.  This is a sample company that has sample data.  If you are new to Microsoft Dynamic GP or querying, I would recommend installing Fabrikam as a means to practice without harming your company’s accounting data.

For any install of Microsoft Dynamic GP, you will have one Dynamics databases and one or more company databases.

Tables

Along with the install of a database, tables are also installed.  As can be seen in this screenshot:

Each table represents a data-set that is used in Microsoft Dynamic GP.   Please refer to my Table Reference page for more information on specific tables that are useful in Microsoft Dynamic GP.

Data is sorted in different tables within databases.   For example, in Microsoft Dynamic GP, one table GL00100 is the Account Master.  In this table, you will find the stored information for accounts.  Table GL00105 is the Account Index Master.  Every record in the GL00100 has a corresponding record in the GL00105 table as the Index Master is used to ensure data integrity.

Rows & Columns

Rows and columns store the actual data.  A column breaks the information down into categories.  If you have an address:

Mark White Washington Hill Ave Virginia, VA 12345

The columns would break the information apart.  With this example, there may be a firstname, lastname, address1, state, and zip.  Here is a screenshot of a column in SQL server.  The screenshot is of the batch table SY00500.

Each row represents one record item.  The first record is highlighted in the following screenshot.