4

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.

  • 1
    "Database is SQL Server 2008" Does it have to be? – Richard Dalton Oct 13 '15 at 15:08
  • 1
    The answer is going to depend greatly on your tolerance for "sparse matrices." – Robert Harvey Oct 13 '15 at 15:37
  • N.B. the choice between a sparsely populated table and a series of disparate child tables is not as stark as you might think. You could always have a view to roll up the child tables if you just wish to inspect the data. – Robbie Dee Oct 13 '15 at 15:40
4

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.

  • 1
    +1 XML is great for providing an interface between the DB and other layers but rarely is it a good idea to store the apples with the oranges. – Robbie Dee Oct 13 '15 at 16:32
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

enter image description here

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.

  • I find it hard to understand your answer. What's a "link table"? What's "earmarked for the type" ? – Tulains Córdova Oct 13 '15 at 15:58
  • A link table is a table that is there purely to service a relationship between tables. You may know it by another name. – Robbie Dee Oct 13 '15 at 16:01
  • I fail to see the advantage of a many_to_many relationship between the master table and each adjustment table. A simple ER diagram would help better convey the idea. – Tulains Córdova Oct 13 '15 at 16:04
  • By earmarked for the type I mean that in the link table, there may be a range of primary keys allocated for each adjustment type so the adjustment type doesn't need to be stored. So from the OP's original question, 1-10000 may be earmarked for product and quantity, 10001-20000 for document id etc. – Robbie Dee Oct 13 '15 at 16:04
  • How would than range of PKs be stored? Min/Max values for each type? – Tulains Córdova Oct 13 '15 at 16:09
0

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).

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.