2

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 Reg<register-code>_<register-field>.

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?

  • 2
    EAV comes to mind, but before you implement that strategy, make sure that it makes sense for your particular application. There are some significant tradeoffs. – Robert Harvey May 23 '16 at 21:39
  • 1
    Search for some of the new nosql features of sql server – JeffO May 24 '16 at 2:09
  • @RobertHarvey Thank you for the clue! In fact, after reading the arcticle you stated, the approach I thought ("metadata/key-value" tables) seems like an EAV, even though I've never heard about EAVs. I'll study this carefully. – cezarlamann May 24 '16 at 14:08
  • @RobertHarvey could you post your comment as an answer (with all your considerations)? – cezarlamann May 24 '16 at 14:46
  • 1
    @CezarLamann: I don't know anything about Mark 21, and am therefore don't feel qualified to properly advise you. The considerations are already documented in the Wikipedia article; you would use EAV when the number of attributes (properties, parameters) that can be used to describe your entities is potentially vast, but the number of attributes that will actually apply to a given entity is relatively small. EAV complicates querying and reporting, because you have to query for your entity and attribute names first. So every join has at least two conditions. – Robert Harvey May 24 '16 at 14:51
1

I'm wondering if what you are trying to do is actually going to make things extremely difficult. Specifically what you say about coupling the data to an existing SQL server DB.

This makes me think you want to effectively run two databases. Whilst doable, issues with querying, synchronisation and performance will result in large amounts of code, bugs and other headaches.

What might be a better solution would be to regard the SQL server DB and a source of legacy data which you need to import into your new database as part of going to production. Then having a single database will vastly simplify what you are doing. And simplicity is the key to all of this.

Taking this approach you can quite viably consider NoSQL or some other engine that will best suite your purposes. The only complexity being the import filters you will need to write to read the only data, format it, and store it in the new database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.