Enhancing Attribute properties in the Model

Idea created by cbeazley.1 on Oct 20, 2017
    • dott2.2
    • skern1.1
    • cbeazley.1

    I am not sure if this has been brought up before or not.  We would like to see some additional flexibility added to the ERD functionality related to Attribute definition.  We would like the ability to define a column as null/not null separate from Optional so that we can define the business rule via the Optional field and define the physical rule via a Null field.  There is a separate field on the TD screen for null/not null; however, anytime we have to modify a column or the keys to the table, we have to drop the TD and recreate it.   Therefore, we have to remember to remake the changes which is a manual process.  Sometimes the information is lost and we have to start over.  Having that on the ERD Attribute screen would allow Gen to preserve that separate choice.  The initial TD for a table or column is based upon the ERD defaults, so putting it on the ERD side will preserve the data.  Also, we would like to ability to define a default value for an attribute without having to define permitted values so that when the DDL is generated the DBMS DEFAULT statement is generated.  Again, this should be on the ERD side.  Example of the DDL we are looking to generate, in the CREATE TABLE statement we want the column statement to be “COLUMN_A VARCHAR2(1) DEFAULT 'I'”.  This has to be manually added after the DDL is generated and again could be lost if we have to regenerate the DDL from the model.  Adding these two options will provide greater flexibility for column definitions that could then be deployed in the TD. 


    There are multiple business reasons for this request.  Our product is sold to clients which deploy it at their site.  We produce reports from the CSE based on the ERD to tell the clients what is required on the table and what is not from a business perspective.  The Optional field is meant for this purpose.  However, we don’t want nulls in these fields, so defining everything as mandatory defeats the purpose of the Optional field from a Business rule perspective.  Second, by making these fields optional which results in a TD that allows nulls, requires us to add additional code to check for nulls or the domain default (i.e. for text, null or spaces).  This complicates the code.  Finally, being an open database architecture, the clients are allowed to add data to our tables via tools such as SQLLoader, SQL, etc.  By allowing the DDL to generate the DEFAULT statement, this will prevent the clients from inserting null values into the columns because the DBMS will set the default values if they are not supplied by the clients.  This prevents abends from happening when our code access that data.  If you have any questions, please let me know.