1

I have a system that acts like a message hub. It normalizes data and stores it to be picked up by a different system. Currently I convert my object model into tables and columns for storage. I came to realize that a good deal of the data is being stored in columns and tables but its not really doing anything useful by being separated like that. My system only does searches and indexes on the highest level of the data model. Could I save time and complexity by storing the data that is not used for searching as just serialized objects?

For example an a basic Order. It has many child objects like a collection of items that were ordered, shipping information, etc.

My system only looks at the top level part of the order to do it routing and logic the rest is just normalized and stored for pickup later.

My idea is to store the top level order information like order status, order recieved date, where the order needs to go etc. as standard columns and tables if need be. But the details which can vary greatly between systems are stored as serialized blob. They are only rehydrated when they are actually sent somewhere else.

Here is a class in C# that would sort of describe the idea.

 public class Order
 {

  public string OrderStatus { get; set; }//actual column 
  public DateTime OrderRecievedDate { get; set; }//actual column 
  public DateTime ShippedDate { get; set; }//actual column 
  public int DestinationId { get; set; }//actual column 
  public List<OrderItem> OrderItems { get; set; }//blob column, serialized
  public ShippingInformation ShippingInformaiton { get; set; }//blob column, serialized
 }

 public class OrderItem
 {
   public string Sku { get; set; }
   /*
   * Fifteen or 30 other properties
   * some are other classes with their own set of properties etc.
   */
   public AnotherObjectClass MoreInformation { get; set; }
 }
 public class ShippingInformaiton
 {
   public string TrackingNumber { get; set; }
   /*
    * fifteen or 30 other properties
    */
 }

The table would then look like this:

 CREATE TABLE [dbo].[Table1]
 (
  [Id] INT NOT NULL PRIMARY KEY, 
  [SourceSystemOrderId] NVARCHAR(50) NULL, 
  [OrderStatus] NVARCHAR(50) NULL, 
  [OrderRecievedDate] DATE NULL, 
  [ShippedDate] DATE NULL, 
  [DestinationId] INT NULL, 
  [OrderItems] VARBINARY(MAX) NULL, 
  [ShippingInformatin] VARBINARY(MAX) NULL
 )

My system is just storing this information as a holding area and then passing it off after performing some logic. Most of it is not going to be searched for or indexed outside of the top level object/table.

Am I going to have an issue with data storage of the serialized object?

--Is that storage worse than the overhead of nested tables and null columns?

Performance wise is pulling a serialized object out of database storage better that pulling out 10 tables and child tables worth of stuff out?

1

Based on "the details which can vary greatly between systems..." a relational database may not be an ideal choice. Your concern over performance would be another reason.

It just doesn't seem like this application is going to take advantage of a normalized relational database. I wouldn't try to force it, but if you have to use this database, then dump these detail records in some blob or possibly and xml field if that's the original format. No need converting it back and forth if you don't need it.

  • I am still using the relational data for searching but the details, of which my routing system does not care, will be dumped into blobs. – Dan Jun 20 '17 at 13:47

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.