0

I am a (poor, young) scientist working to develop a database to allow a project to scale up. I am experimenting with MySQL and have had very encouraging experiences so far, but I am still novice and am coming at this from perhaps an unusual perspective.

Below is a diagram of the relevant parts of my schema, as I have it modelled right now. The fields are essentially place holders at this point, but in general I would like to leave room for documentation of just about every unique element in the database (except probably not for jobs, but I digress). There are a couple of qualities that I am shooting for:

  1. Valid parameters are algorithm dependent, and we may add new algorithms at any time. I'd like to be able to register a new algorithm, and associate it with particular parameters.
  2. Because new algorithms may require new parameters, I should be able to register new parameters as well.
  3. A particular experiment will use one algorithm. Jobs are unique parametrizations of an experiment/algorithm. Through the relation of an experiment and an algorithm, I can validate the parameters submitted along with a particular job.

My point of confusion, given this schema, is how to store the values of the parameters for a particular job. Right now, as a place holder, I have a values column in the table job_has_parameter, but this is not going to work. Some parameters are integers, others doubles, still others character strings. Some algorithms may even take lists as parameters... but I'm willing to punt on that for now. At any rate, a single value column is not going to work.

Does this schema make sense at all? If it does make sense, generally speaking, what would be a reasonable way to handle storing the parameter values? I welcome criticism; I am under no delusion that I am on the right track here, but it's an effort.

enter image description here


Edit: I think that this schema is inadvertently adopting an entity-attribute-value style. I read that this is a bad thing. https://www.simple-talk.com/sql/t-sql-programming/avoiding-the-eav-of-destruction/ Perhaps someone can help set me on a better path?

  • EAV isn't always a bad option (I'm not saying it is bad/good/ for your purpose, just trying to point out EAV isn't universally bad). Checkout this blog article for a balanced view. The article is written with SQL Server in mind but the concepts should translate well. – Erik Sep 10 '15 at 16:03
  • 1
    EAV is at its best when you cannot explore and analyze the subject matter. EAV is at its worst when you could analyze the subject matter and understand the data, but opt not to, in order to save time and money, before building. – Walter Mitty Nov 5 '17 at 1:00
0

How about adding an extra column in you Job_Has_Parameter table?

Add a column called DataType and store what type of value the text contains.

When it comes to using the value, you might have to use the combination of case statement and cast/convert function.

You can take it to next step and add a table called datatype and make a foreign with this new column.

  • Are you suggesting that this schema overall is reasonable, and building out a type-casting mechanism would be reasonable as well? I am not being facetious---I don't have a good point of reference. – Chris Cox Sep 10 '15 at 15:47
  • I am not commenting on the overall structure but this is the option I can think of with minimal changes in the current environment. – mouliin Sep 11 '15 at 13:55
0

Overall, I think your model is pretty good. I'm going to point out that the project you have chosen for your introduction to databases is a pretty difficult one. Don't get discouraged, but don't expect it all to fall into place in one fell swoop.

Essentially, the algorithms that are parameterized in your database will all be processed by some sort of master algorithm that adapts itself to the job at hand by reading the parameters that pertain to that job and and adjusting things accordingly. Great stuff, if you can pull it off.

Now down to the specifics of the question you asked: how to accommodate parameters of different types. This is a classic case of types and subtypes, although I prefer the terms classes and subclasses. This question surfaces over and over again in relational database design, usually in different guises. Dozens of questions have been asked and answered in here regarding the issue of subclasses. These questions are superficially quite different, but they all boil down to the same thing.

One problem is that the relational model doesn't fit well with the concept of inheritance, and inheritance is the elegant way to express a class subclass relationship. There are some well known workaraounds, one of which may fit your case.

Do a web search on the following buzz phrases:

Simple table inheritance
Class table inheritance
Postgres Table Inheritance

You'll find a wealth of articles dealing with how to design tables that capture the essentials of inheritance. however, using these tables will require some careful programming.

In your case, here is the way I would go:

Remove the values field from the job-has-parameters table.
Have separate tables for different types of parameters. I'll limit my discussion to two types, text and number. But you get the idea. In the job-has-text-parameters table, you have jobId and ParameterId, and value. Value is, of course, of type text. JobId and ParameterId are, taken together, the primary key of this table.

Likewise, you have the job-has-number-parameters table you have the same three fields, except that value is now of type number. You probably also need a type field in the parameters table to indicate what type that parameter must take when new jobs use it.

Don't worry too much about the overlap between your design and EAV. Basically, your design is more dynamic than the classical relational database learning projects teach. And most of the criticism of EAV comes from the fact that it attempts to be completely dynamic, thus making the data definitions invariant to a changing subject model. The downside of this is that it makes the data very hard to turn into useful information.

But in your case, the dynamic quality of parameters is not just a design choice, but rather it's fundamental to the subject matter itself, unless I miss my guess.

Good luck.

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.