OSCF schema adoption by RDBMS databases? #424
-
|
Can OCSF schema be created and deployed in RDBMS data bases like Snowflake and Oracle? What could be the issues in this approach? |
Beta Was this translation helpful? Give feedback.
Replies: 1 comment 3 replies
-
|
Hi @debaduttar , yes absolutely. The schema can be used in a document / nosql style database but can also support a traditional RDBMS, @sfc-gh-osinger has been involved from the Snowflake side. Structuring the schema in an RDBMS (the traditional way) would be a matter of creating the different tables for the various objects, classes, and associated enums etc. and then creating the necessary relationships between them. There are two main design issues to consider that come to mind. Probably the biggest design issue is ensuring there is a way to handle changes in the schema between versions. OCSF defines breaking changes from a producer perspective, and major versions are expected to be backwards and forwards compatible with the associated minor version. Some of these changes will break a traditional RDBMS schema and so there needs to be some mechanism to handle that. For example, one of the non-breaking changes is the addition of an optional attribute. Attempting to insert data into a column that does not exist in an RDBMS will fail, so there needs to be a mechanism in place to handle those types of scenarios. A few options would be to use vertical rather than horizontal tables, use a vertical table for the attributes that are sent where columns do not exist, drop the attribute etc. The second design issue is touched on above, and that is how to handle attributes in OCSF that can send arbitrary attributes. The only one I can think of at the moment in the main schema is Using extensions presents similar design considerations. Hope that helps 🙂 |
Beta Was this translation helpful? Give feedback.
-
|
Hi @jp-harvey , From Snowflake prospective, although RDBMS constructs like tables can not be ignored, with data types like Variant ( for storing JSON data), Objects and Arrays, it should be better to manipulate (semi/un)structured data than traditional RDBMS way. @sfc-gh-osinger any advice will be much appreciated. |
Beta Was this translation helpful? Give feedback.
-
|
Omer did mention a while back that there were some special features of Snowflake (that I'm not familiar with) that could help in this regard. Something about dynamic fields created from JSON structures, which sounds a bit like extending an RDBMS with nosql features which sounds pretty awesome. That might be the |
Beta Was this translation helpful? Give feedback.
-
|
Thank you very much for your feedback. |
Beta Was this translation helpful? Give feedback.
Hi @debaduttar , yes absolutely. The schema can be used in a document / nosql style database but can also support a traditional RDBMS, @sfc-gh-osinger has been involved from the Snowflake side.
Structuring the schema in an RDBMS (the traditional way) would be a matter of creating the different tables for the various objects, classes, and associated enums etc. and then creating the necessary relationships between them.
There are two main design issues to consider that come to mind. Probably the biggest design issue is ensuring there is a way to handle changes in the schema between versions. OCSF defines breaking changes from a producer perspective, and major versions are expected to be bac…