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:
- 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.
- To have 1 table per type of adjustment, reference all these tables to the one master table and add tables as required.
- 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.
Best Answer
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.