SOLIDWORKS PDM Database: Query Planning

This is part of a series of articles aimed towards providing the basics of creating queries for SOLIDWORKS PDM vault databases. Administrators will be able to collect data for their own purposes that are not attainable from SOLIDWORKS PDM out-of-the-box search features.

This article will introduce a process of planning for creating basic SQL queries. There are many ways to plan and techniques vary with experience and training. However, this is a method that we have found to be more consumable and can be quickly put into practice.

What this document is not 

This article is not intended to teach you how to query databases. Learning SQL is beyond the scope of this document. This document is intended for the PDM Administrator who would like to use his or her current SQL knowledge to query PDM vault databases. Moreover, advanced vault database queries would require advanced planning and experience and are also beyond the scope of this article. 

If you would like to learn SQL, we recommend visiting w3schools for their free online training. GoEngineer is not affiliated with w3schools, we just think their website is awesome.

Where do I start? 

To have an idea of what tables to use the relation of, you need to first understand how the tables relate to one another. Once you have an idea of what relations you need, you will be able to create your query and dismiss unwanted relations to get the correct data. Luckily, Microsoft SQL Server Management Studio (SSMS) has a tool to create diagrams of the relating data between tables. Once you see a “path” of the relating data can use such path to build your query. 

By the way, we have created a document that provides the steps of creating a database diagram and can be found here. 

Let’s Plan a Query 

To see how the vault database tables relate to one another we will run through an example. For this to work, try to follow the image below as you read this scenario. 

Suppose that based on a known file name you want to query for the corresponding path to that file. Through the creation of a diagram you find that the “File Name” column is part of the Documents table, “Path” is a column that is part of the Projects (folders) table, but you need DocumentsInProjects table to connect the other two tables. This is because the DocumentsinProjects contains both the DocumentID (which the Documents table has) and the ProjectID (which the Projects table has). 

You then notice that LockProject in the Documents table has a direct connection to the Projects table. However, this relationship is not needed since LockProject is used for other purposes in PDM that is not what you are looking for. There is only one path from this point and you, therefore, plan your query as follows: (Green Arrows) 

File Name (Documents table) > DocumentID (Documents table) > DocumentID (DocumentsInProjects table) > ProjectID (DocumentsInProjects table) > ProjectID (Projects Table) > Path (Projects Table) 

Note: In the image above that the boxes are SQL tables, and every value listed per row are columns in the table.

Omitting how the query was created, for now, the plan in this example will produce the following query:

SELECT Projects . Path

FROM         Documents      INNER JOIN

                                      DocumentsInProjects ON Documents.DocumentID = DocumentsInProjects.DocumentID INNER

JOIN

                                     Projects ON DocumentsInProjects.ProjectID = Projects.ProjectID

WHERE (Documents.Filename LIKE N'MySOLIDWORKSPart.SLDPRT’)

Notice the similarities between the query above and the planned path – food for thought.

In the next article, we will build the query above using a tool that will help you create most of your queries for SOLIDWORKS PDM vault databases.

More PDM Articles

Automatic SQL Backup - SOLIDWORKS PDM Standard

SOLIDWORKS PDM Vault Clone Procedures

Change SQL from Full Recover to Simple Recovery Model