Forum Stats

  • 3,874,734 Users
  • 2,266,768 Discussions
  • 7,911,961 Comments

Discussions

Extended Properties for schema objects

Jeffrey Kemp
Jeffrey Kemp Member Posts: 193 Bronze Badge
edited Oct 30, 2018 4:18AM in Database Ideas - Ideas

At the moment Oracle database allows developers to add a single Comment to tables, views, columns, as well as a handful of other object types.

SQL Server supports the concept of "Extended Properties" which allow unlimited name/value pairs to be added to almost any schema object including:

  • Database (== Oracle schema, I believe)
  • Stored Procedures
  • User-defined Functions
  • Table
  • Table Column
  • Table Index
  • Views
  • Rules
  • Triggers
  • Constraints

https://www.mssqltips.com/sqlservertip/5384/working-with-sql-server-extended-properties/

"Extended Properties can be used to:

  • Specify a caption for a table, view, or column.
  • Specify a display mask for a column.
  • Display a format of a column, define edit mask for a date column, define number of decimals, etc.
  • Specify formatting rules for displaying the data in a column.
  • Describe a specific database objects for all users."

I can think of other uses for this sort of thing as well, primarily to do with systematic markup of attributes to be used for automated code generation.

Of course, one could create a table and an API package to build something like this, and you'd then need a DDL trigger to detect dropped objects, etc. but it would be great to have this available as a builtin feature of the database.

AndrewSayerUser_D6ENFSven W.Mike KutzGregVctriebLothar FlatzberxPeter Hraško
11 votes

Active · Last Updated

Comments

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,945 Red Diamond

    Dislike it as there are no clear borders between something like a format mask as an extended property, versus NLS settings of the client.

    Collisions will frequently occur, without a meaningful and consistent way to address these.

    BEDE
  • Mike Kutz
    Mike Kutz Member Posts: 6,254 Gold Crown

    Jeff,

    I feel that this Idea is a duplicate of .

    Your Idea has a cleaner is description.  But, I feel they both accomplish the same thing.  Adding ad hoc information to the database objects in a key-value pairs.  (personally - JSON format)

    I do agree on its usefulness in assisting in code generation.  eg:  "this column is for VPD"

    Certain information could be used by frameworks.  "use package ABC_PKG as the TAPI/XAPI for this TABLE/VIEW".

    FYI - You'll want to also.

    MK

  • Jeffrey Kemp
    Jeffrey Kemp Member Posts: 193 Bronze Badge

    Jeff,

    I feel that this Idea is a duplicate of .

    Your Idea has a cleaner is description.  But, I feel they both accomplish the same thing.  Adding ad hoc information to the database objects in a key-value pairs.  (personally - JSON format)

    I do agree on its usefulness in assisting in code generation.  eg:  "this column is for VPD"

    Certain information could be used by frameworks.  "use package ABC_PKG as the TAPI/XAPI for this TABLE/VIEW".

    FYI - You'll want to also.

    MK

    Thanks, I thought I'd seen a similar idea but couldn't find it. It is basically the same idea.

    User_D6ENF
  • Jeffrey Kemp
    Jeffrey Kemp Member Posts: 193 Bronze Badge

    Dislike it as there are no clear borders between something like a format mask as an extended property, versus NLS settings of the client.

    Collisions will frequently occur, without a meaningful and consistent way to address these.

    This idea has nothing to do with NLS settings. The attributes will not be interpreted in any way by the database or the client, they will be up to our application programs to use how they wish.

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,945 Red Diamond

    This idea has nothing to do with NLS settings. The attributes will not be interpreted in any way by the database or the client, they will be up to our application programs to use how they wish.

    Then why not have the app layer create and maintain its own metadata?

    Why expect the database to provide this as name-value pairs, as part of its metadata?

  • Jeffrey Kemp
    Jeffrey Kemp Member Posts: 193 Bronze Badge

    Then why not have the app layer create and maintain its own metadata?

    Why expect the database to provide this as name-value pairs, as part of its metadata?

    Because it's metadata about the database objects. The database is the perfect place to store this sort of data, for a whole lot of reasons.

    Yes, you could store this data in the application tier. But then you could also implement constraints in the application tier as well - and many do. Thankfully, however, most of us know it's better to implement constraints close to the data and at that layer.

    The metadata might be useful to more than one application. It might also be metadata that is useful to application code that is running on the database (e.g. in stored PL/SQL).

    Another point is that when the database object is dropped, the metadata about it should be dropped as well.

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,945 Red Diamond

    Because it's metadata about the database objects. The database is the perfect place to store this sort of data, for a whole lot of reasons.

    Yes, you could store this data in the application tier. But then you could also implement constraints in the application tier as well - and many do. Thankfully, however, most of us know it's better to implement constraints close to the data and at that layer.

    The metadata might be useful to more than one application. It might also be metadata that is useful to application code that is running on the database (e.g. in stored PL/SQL).

    Another point is that when the database object is dropped, the metadata about it should be dropped as well.

    Disagree as I see it as application metadata about database objects.

  • Jeffrey Kemp
    Jeffrey Kemp Member Posts: 193 Bronze Badge

    Disagree as I see it as application metadata about database objects.

    I'm fine with agreeing to disagree. IMHO you seem to have a rather narrow view of the usefulness of the idea, but that's ok.

    Cheers and thanks for your input.

  • Mike Kutz
    Mike Kutz Member Posts: 6,254 Gold Crown

    Disagree as I see it as application metadata about database objects.

    As I attempt to create a retort, I am realizing that this idea is too small to solve a much larger problem.

    In order to create templates (for template based code generators) that conform to the design philosophy, we need information about the Logical Model, the Physical Model, the relationship of Entities to DB Objects, and all of the "Markup" data that can be added to those models. ( "markup" === https://www.thatjeffsmith.com/archive/2016/10/user-defined-properties-in-sql-developer-data-modeler/  )

    This Idea would only allow adding Markup information that exists at Physical Model level.  (I'm only suggesting that this information be an extension to the Data Dictionary Views.)

    I'm keeping my Up Vote as it will allow people to add appropriate Markups to existing DB Objects without the need to (re)start at the Logical Model level.  I see it as an interim solution to the bigger problem.

    With the appropriate Markup information, we (template developers) could still develop some interesting XAPI templates.

    Yes: the SDDM interface to code generators (ie the Java Class used as parameters to the call to the generators) includes the Markup information.  However, one should be able to run the templates outside of any Data Modeling software.  That means : all of the information (about a logical & physical model) needs to be available as tables/views from within the database.  Additionally, the data needs to be Data Model Software agnostic.

    Jeff : I am starting to believe that it would be better to create the templates based on information about the Logical Model (with Markup) than base the templates off of the Physical Model (with assumptions).