How to store records with variable structure?

Question

We have an old system to manage inventory adjustments that is going to be remade. There are different type of reasons to make an adjustment, and each type requires different data to be filled. Some only require the product and quantity, others require a document ID, others require two products per adjustment, and so on.

Now, in the current system, the data is stored in a master-details tables structure. While the master table represents no major problem, the details table was originally designed with all the possible fields required for the original 5 types of adjustment. So, many records don't use all the fields. But, as the number of adjustment types increased, it has been troublesome to adapt the data required for these adjustments to the current structure, using fields for a different purpose that they were designed for, or storing more than 1 value per field. This is the kind of bad design we're trying to avoid with the new version.

Now, considering that there are currently 15 different types of adjustment, and that this number can increase, what would be the most efficient way to store this data in terms of accesibility and Read/Write performance? Keeping in mind that most of the data processing will be made in the application, we have two possible approaches:

1. To have the main data as "normal" fields (i.e. primary and foreign keys), and the rest of the data in a XML field, having the XML the required data depending on the adjustment type.
2. To have 1 table per type of adjustment, reference all these tables to the one master table and add tables as required.
3. Any other option we have not considered yet

Database is SQL Server 2008 RS and application will be on .NET (C#). We already have a few pros and cons for each option, but it is yet to be decided if database access will be mainly via EF or Stored Procedures (as the old system), and I'm aware each way can fit differently with how data is stored.

Answer 1

I usually go with the multiple table approach.

You might find however, that in terms of read/write performance your current single table format outperforms both alternatives.

XML columns for structured data in a relational database are, I think, trying to do the same job as tables and columns. Before you head down that route, consider the problems you may encounter when in the future you need to change the format of the data.

Answer 2

I would suggest you retain the master table as you've described and have a table for each adjustment type. In between, have a link table with a FK to the master table and a column for the adjustment type and a FK to the primary key on each adjustment table. Naturally, you'd also want a metadata table to store adjustment types and their descriptions.

ERD

You might ask why you couldn't just have a FK to Master from the child tables. A valid point, but this design affords you the opportunity to apply the same change to multiple Master records without introducing redundancy.

I've seen some implementations similar to the scenario you've described where the type is omitted on the link table for the sake of brevity and a series of adjustment table PKs are earmarked for the type. I'd strongly advise you not to do this as some adjustment types may grow far beyond what you'd anticipate.

Answer 3

I think a lot depends on your anticipated data volume. If you're only going to have a couple thousand of inventory adjustments a year, then let the implementation LOE determine what method to use. If you anticipate higher volume, then you'll probably wind up getting better performance by going with separate tables (especially if you have to search or report on some of the variable data fields). I know SQL Server has some built-in support for XML in database records, but it didn't seem to support complex queries IIRC, and performance will be slower if you don't have the space to devote to index support. I'd also suggest looking at JSON instead of XML if you're going to be storing a lot of data in the variable fields, it's a lot easier to parse (although you do lose the strong typing of XML Schema).