Here I have a very basic ILS (Integrated Library System) done a long time ago (made with C#/SQL Server), in production for years. But now I have a demand of make this system MARC 21 standard compliant, and with this demand came the doubt: Since this standard have a lot of register types, and each register type may have a lot of optional/variable fields according to the type of publication (book, music notations, periodicals, etc.), how could I design this?
My first idea was to have some "metadata" tables. These tables will contain register types, with another ternary table with all possible registers for each publication type, according to the docs. These tables will join with another table containing all possible subfields for each register, and with another one for all possible values for each "fixed-type" subfield... A lot of joins I know, but MARC 21 have a lot of things with fixed types or default values.
The real publication data (with all variable fields) I thought that I could store in a key-value table (denormalized, where a key is a composed value with
<Publication-Id><Register-code><Subfield-Id>) or in a huge table with all possible register and all possible fields, where each column could be named as
I know that I could take the "NoSql pathway" and "sing along" with all my dynamic data, but I will need to couple this structure with an existing SQL Server db.
I already have some normalized tables (i.e.: Authors, Publication types and so on)
How should I design this?