Thursday, 15 January 2015

serialization - How to store value objects in a relational database? -


I am working with a large project that has many objects that represent simple (non-related) values . Sometimes these values ​​are a string, sometimes they are two strings, sometimes string and int ...

We currently have a 'value' table in a relational database that contains columns Are: id , class , string1 , string 2 ..., int1 , Int2 ..., double 1 etc. It is convenient, but there is a disturbance.

Values ​​have all the following properties:

  • The object has the same properties (i.e. typed) with the same class .
  • No objects are related (only the key is Id primary key).

    How do we navigate away from this glitch? As I see it, our options are as follows:

    1. Just keep adding columns, and forget the glossary mapping between the table and the object. Just stack it up.
    2. Create a new table for each value object. This will add a large number of tables to the database, many of which will be less than 6 rows. I am concerned about the noise that all these extra tables add to the database.
    3. Deploy schema free databases for these objects (in reality our deployment scenarios are unlikely). Create a table with
    4. id , class column and a blob value column and sort in the Value object value column . Is it viable?

      Our options Are there any warnings or disadvantages to use the serialization? Is there any alternative I do not know? Most welcome.

      I stumbled over it and navigated on it. Although it is too old, I was astonished to answer because it not only allows a very well written problem but also allows for an argument on a database denormalization .

      Due to more and more excuses to be denormalized for a database, performance may be the most important, but difficulty in data classification (such as handheld problem) is definitely Besides the most common, there can be a denormalized several ways , and a good deal of them are addressed by OP.

      The fact that, a database should be denormalized as last resort , everything has failed due to its:

      • Data for humans is also wasted Also RDBMS is difficult to understand, or even remember, anyone whose The purpose of the field named Integer1 is or is a random value that could possibly catch anything and RDBMS results are sequential In or order to implement the joint can not remove values ​​from serial institutions. It is difficult to maintain an unstable schema. Other, higher levels are dependent on that. If the schema changes overnight, then the app should also be changed to reflect the new situation. Worse, the more difficult it is to maintain views, stored procedures and other dependent database components.

      • The constraints can not be enforced, can not be indexed Anybody defining a serialized field as a foreign key Does not make sense, or has been limited to a specific set of values. It can cancel a great deal of database self defense mechanism. Less data integrity means more administrative costs. In addition, an index will be as useless as it would be, which would be less open for optimizing the table.

      • Metadata will eventually be stored as data . Imagine a multilingual CMS with a main article table for placing articles, now for each language supported, the translation (i.e. article_en , article_fr , article_es , article_es ) / code> etc.). To record existing translations of articles, a "relationship" table should be created with a foreign key for article table, a language ID, a table name for the translation table, and an area of ​​translation The table should have an FK but can not be defined as one Then, try writing a query that calculates available translations for each article!

        So, if possible, if the institutions can be classified to an extent, then the answer can be. To support common properties, or when classification is not only appropriate, with foreign key in the table holding generalized data, it is more than enough sacrifice.

No comments:

Post a Comment