At FastChangeCo, the data modelers within the Data Management Center of Excellence (DMCE) team are constantly designing new database objects to store data. One of the data modelers on the team is Xuefang Kaya. When she takes a new user story/task, she usually models multiple tables, their columns, and specifies a data type for each column.
For example, if a column needs to store the timestamp for an event, Xuefang sets the date/time data type for that column (in that case, it even depends on the database in each case). If additional columns have to store names and addresses, it sets the columns to the ‘text’ data type.
Change the default value in PowerDesigner or add a new one
For a new table ‘Student’, to record the students working in the team (yes, I know, an ole example), Xuefang wants to set a so-called default value for the column 'Inserted At' in the database design. A default value can be the return value of a function (e.g. DB_NAME() in Microsoft SQL Server) or a constant (e.g. The text ‘Value is not defined’).
This way, even though the 'Inserted At' column (with the specified default value) is not specified in the insert statement (DML), the specified default value can be inserted automatically. Typical examples in previous implementations of the DMCE team are timestamps or the user names to document who inserted the data into the table and when.
In the given table 'Student' the task is to automatically add the UTC timestamp to each record in the column 'InsertedAt'. In the database design to be created for Microsoft SQL Server, Xuefang wants to use the function SYSUTCDATIME() as 'default value' for this. Xuefang knows that in PowerDesigner it is possible to select a default value when designing a column.
However, Xuefang quickly discovers that the required SYSUTCDATIME() data type is not available for selection.
As an obvious solution to this problem, Xuefang creates the missing default data type in PowerDesigner as a ‘Default Property’.
At first glance, this looked like a good and simple solution for this requirement. However, Xuefang quickly discovers that this approach has more drawbacks than expected in this case:
- The default data type is not immediately available in all data models of the DMCE. Only as a Default Property in the current data model.
- PowerDesigner creates the function SYSUTCDATIME() in the default database object (CREATE DEFAULT) as a text constant, not as a function. This results in the text 'SYSUTCDATIME()' (including the apostrophes) being inserted rather than the current UTC timestamp.
- The database object created with the DDL CREATE DEFAULT is obsolete and will most likely be removed in a future version of Microsoft SQL Server (CREATE DEFAULT (Transact-SQL) - SQL Server | Microsoft Docs).
These are too many possible error sources for Xuefang and the DMCE team. Another solution must be found. After some research, Xuefang finds an entry in the PowerDesigner help for the topic default value of a column, which is promising:
Default - The value assigned in the absence of an expressly entered value. For the PDM, you can directly enter a default value or select a keyword (defined in the Script\Sql\Keywords\ReservedDefault entry of the DBMS definition file) from the list. Default objects (see Defaults (PDM)) are also available for selection if your DBMS supports them.
Before you continue here, be sure to read the blogpost DBMS resource file! This is the base for the further procedure.
"Great," Xuefang says to herself. This is how we do it. The DMCE team had already made adjustments to the RDBMS resource file. This custom-built RDBMS resource file is now fully customizable according to their needs and requirements. Xuefang can use it to solve two of the disadvantages mentioned above. The file is already distributed through the Git repository across the DMCE team and used in all data models. Thus, the customization would be immediately available to all.
"OK, then I will implement it like this." To do this, she opens the DBMS Properties dialog in PowerDesigner via the Database -> Edit Current DBMS... menu.
And adds the function SYSUTCDATETIME() in the ReservedDefault section.
After saving, Xuefang can select the new default value in the properties of the 'Inserted At' column.
After all the tests have been successfully completed, the DMCE team is excited to have found a simple solution to an annoying inconvenience at the end.
The DMCE team is so stoked with this approach that they would like to fix more of the problems, inconveniences and bugs with PowerDesigner DDL file generation this way.
But that's another story. More about this in the next article of the series. You definitely have to stop by again.
Back to the PowerDesigner series: PowerDesigner Series