3

I've started working with an application that has an usual design:

In addition to the tables that contain the actual data, there is one metadata table that contains all the information about the other tables: their names, their columns, which columns link them to which other tables, and so on... all in a single table.

The application mostly queries this table to figure out how to query for other entities, how to query or update related tables, how to insert, delete.

The result of this is that the application's data access code is quite generic. Most of the data access functions just take a few parameters which represent the names of things (concrete tables and columns) to look up in the metadata table.

The main drawback, as far as I can tell is that when the schema changes, the concrete structures and the content of the metadata table need to be kept in sync or the application could break, although it seems that the metadata table is generated from a custom-written script that also creates/alters the other tables. Maintenance of that script and the original data model is another concern.

I also have to consider that this data access component may either replaced or preserved for new applications. If it were to be replaced, it would probably be with something more "normal" like hibernate or some other framework. I'm quite torn on which way to go since the current code, while a little odd-looking, seems to work fine.

Are there any hidden advantages/disadvantages that I might have missed that might help me make a decision?

Actually, what is this design pattern even called, in case I want to look up other implementations of it? I don't think I've ever seen anything like it before, I don't really know what to call it or how to research it.

Improve this question
7
  • 1
    So every query actually requires 2 queries? – Max Vernon♦ Jun 8 '16 at 20:51
  • @MaxVernon: It could... although it also has a sort of cache where it stores partial queries such as select a,b,c from T, where d = ? and then fills it in as a prepared statement. But the first time such a query is run, yes there should be > 1 query. The application is a desktop application so it's not as though there are hundreds of users hammering away at it. – FrustratedWithFormsDesigner Jun 8 '16 at 20:55
  • 3
    Please tell me you're not working on Vision... – Greg Burghardt Jun 9 '16 at 2:57
  • Once I saw another situation like this, for a large public administration. They had a lot of applications, and were very happy about their approach. – Renzo Jun 9 '16 at 4:12
  • @GregBurghardt: 1. Thank you for that facinating read! 2. No, it's not nearly that bad. Thankfully! :) – FrustratedWithFormsDesigner Jun 9 '16 at 14:06
1

I believe this is called a "Big Ball of Mud". The not so hidden disadvantage is the system appears to have done away with normalization of the data model for the metadata. This is likely lead likely lead to inconsistencies over time. However, it is possible that the script you described, just rebuilds it from the database's metadata.

All the databases I have worked with, have a nice normalized data structure for the meta data in place of the single metadata table you are describing. This involves the appropriate tables for the appropriate metadata. Most cache the query and resulting optimized execution plan for recently used queries.

If the code is figuring out the required to query on the fly, by querying the metadata table, it is likely slower than it needs to be.

A query like select a,b,c from T, where d = ? can be supported by a single query with a parameter. This also prevents SQL injection when someone enters a value like 1 or 1 == 1. On a desktop application it may not be that serious, but many companies have learned how serious it can be.

There is a reason many developers use hibernate, Spring, and other data access layers. Some of these can build the database required to support the application, and also work with databases that have already been built.

Improve this answer
4
  • I'm a little hesitant to go as far as to call it a Big Ball of Mud - I've seen far worse with that name and this doesn't seem so bad. But I wanted to read a bit more and see if there were other people doing something similar, and find a better list of pros and cons. – FrustratedWithFormsDesigner Jun 9 '16 at 14:20
  • You are correct, the script will make the concrete tables and the contents of the metadata table conform to the source data model. It could also be used to generate a new set of tables in a new database. This means that the best way to make changes to any tables is to first modify the data model, export to the format that the script wants, then run the script against the database, with the data model file as input. – FrustratedWithFormsDesigner Jun 9 '16 at 14:20
  • I should clarify - the metadata table I'm talking about is not a database-native metadata table such as the INFORMATION_SCHEMA tables in MySQL. This metadata table is generated from a script based on an input data model. It only describes the concrete tables and their structures. – FrustratedWithFormsDesigner Jun 9 '16 at 14:24
  • As for performance and security, the database is accessed from a number of systems, but this particular logic only exists in one desktop application. The other systems use much more conventional queries (but with APIs in other languages). – FrustratedWithFormsDesigner Jun 9 '16 at 14:26

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.