We have a fairly simple table structure, but with a LOT of fields per table (talking 40+). This data is initially produced in plain-text, user-readable tables, but then it is translated into higher-performance, easier to query tables before being installed for use in production.

What we do is, wherever possible and reasonable we translate certain fields into enumerated values, and keep track of the enumerations in a MasterEnum table. There are usually 20-25 enumerated fields out of 40 or so.

Sample table structure:

Plain text version:

    |  PartNumber   |  Manufacturer  |  SomeData  |  SomeMoreData  |  SomeTextData ...
    |  1x9kdah      |  GizmoCorp     | ThisIsData |  OtherData     |  ThisStaysText ...
    |  8xcjkzh      |  GadgetInc     | MoreData   |  OtherData2    |  ThisTooStaysText ...

Target table sample structure:

    |  PartNumber  |  Manufacturer  |  SomeData   | SomeMoreData  |  SomeTextData ...
    |  1x9kdah     |       1        |    1        |      1        |  ThisStaysText ...
    |  8xcjkzh     |       2        |    2        |      2        |  ThisTooStaysText ...

Master Enumeration Table Structure

    |  FieldName     |  InputText  |  ValueCode |
    |  Manufacturer  |  GizmoCorp  |  1         |
    |  Manufacturer  |  GadgetInc  |  2         |
    |  SomeData      |  ThisIsData |  1         |
    |  SomeData      |  MoreData   |  2         |
    |  SomeMoreData  |  OtherData  |  1         |
    |  SomeMoreData  |  OtherData2 |  2         |

We have a means of doing this translation that works and works well; however it's a little on the slow side since all the processing is done in Java via Spring/Hibernate. My question is:

Is there a way to write a single query that would accomplish all the above translations? (Note that we have an excellent way of keeping track of our field definitions programmaticly, so generating complex SQL queries on the fly is not an issue). If it is not possible to do it in a single query, how would I structure queries to iterate over the individual fields and make sure that as the translations happen the data is inserted into the new table remains associated with the correct rows?

Note that it is safe to assume the target table is always empty at the beginning of the process.


As others have pointed out, this is a Really Bad Idea. Still, if you insist, the SQL is not hugely complicated:

Manufacturer    VARCHAR(30) NOT NULL,
Data1   VARCHAR(30),
Data2   VARCHAR(30),
Data3   VARCHAR(30)

CREATE TABLE Translations
FieldName   VARCHAR(30) NOT NULL,
PRIMARY KEY (FieldName, Value),
UNIQUE (FieldName, ID)

Manufacturer    VARCHAR(30) NOT NULL,
Data1   VARCHAR(30),
Data2   VARCHAR(30),
Data3   VARCHAR(30)

INSERT INTO CleanData (PartNumber, Manufacturer, Data1, Data2, Data3)
        TMfr.ID AS Manufacturer,
        TDt1.ID AS Data1,
        TDt2.ID AS Data2,
        TDt3.ID AS Data3
        RawData AS RD
        LEFT JOIN Translations AS TMfr ON RD.Manufacturer = TMfr.Value AND TMfr.FieldName = 'Manufacturer'
        LEFT JOIN Translations AS TDt1 ON RD.Data1        = TDt1.Value AND TDt1.FieldName = 'Data1'
        LEFT JOIN Translations AS TDt2 ON RD.Data2        = TDt2.Value AND TDt2.FieldName = 'Data2'
        LEFT JOIN Translations AS TDt3 ON RD.Data3        = TDt3.Value AND TDt3.FieldName = 'Data3'

Extend to the complete set of fields. May Codd have mercy on your soul.

| improve this answer | |
  • For clarification, why would I use LEFT JOIN as opposed to INNER JOIN? Also, only PN and MFG are non-null in the data, does that affect your answer? – StormeHawke Mar 8 '13 at 20:25
  • By LEFT JOINing, you get the ID of the substitute value, if one exists. I'm assuming that not every value has a substitute, but if that is the case, then you can use INNER JOIN and replace COALESCE(X, Y) with just X. – Jon of All Trades Mar 8 '13 at 20:28
  • Regarding NULLs, good point, I just assumed the fields were NOT NULL. Edited. – Jon of All Trades Mar 8 '13 at 20:29
  • By the time this data gets to the point of running this query, there will be a substitute value. We automatically generate those as needed as well, and that step happens first – StormeHawke Mar 8 '13 at 20:37
  • If, say, Data2 is null for a particular row and I'm selecting X as you mentioned, is the query going to be smart enough to insert null or do I need to add another condition? – StormeHawke Mar 8 '13 at 20:40

Not many people like them, but I would suggest you use a cursor in a stored procedure to loop though the fields and normalise them into the target table (silly ETL process).

You could probably do better having had separate lookup tables for each type of field, but it depends on you scenario.

| improve this answer | |
  • Creating separate lookup tables for each type of field may be possible, though I've already got something like 116 tables for this application, so adding a ton more might be a little painful. I'm not familiar with StoredProcedures - how would that work? And how much maintenance would be involved when you factor in about 40 different tables along the lines of what I described in my original question? – StormeHawke Mar 8 '13 at 17:24
  • Sorry for the late reply. If you are concerned with the amount of tables, then you are probably better off just keeping the one you have instead of splitting it. If you write your stored procedures properly, your maintenance would be minimal (unless new types / methods / fields are added). Here is the basic syntax of a cursor within a stored procedure [link] (dev.mysql.com/doc/refman/5.0/en/cursors.html) – RoKa Mar 13 '13 at 9:16

Could you create a view (or multiple views) that's based on the query that produces the results you want? If the main bottleneck is speed, having spring/hibernate go against a simple-looking view might be better. This might not want to work if you want to write to the "translated" versions of the tables, but I think using views could at least help for situations where you just want to display the data.

| improve this answer | |
  • No, unfortunately a view won't help here for the sole reason that it is indeed that we're writing the translated table. The data from the translated field is never simply displayed, but queried against, and the enumerations are intended to speed up said queries on some really monstrous tables. The values are then reverse-translated on the fly for user display – StormeHawke Mar 8 '13 at 17:22
  • @StormeHawke: It seems that MySQL has the ability to insert/update to a view: dev.mysql.com/doc/refman/5.0/en/view-updatability.html (I have never tried this but maybe it will help you?) – FrustratedWithFormsDesigner Mar 8 '13 at 17:25
  • Since views are nothing more than stored select statements, I don't really see how this could help since my fundamental problem is writing a select query that accomplishes my goals – StormeHawke Mar 8 '13 at 17:42
  • @StormeHawke: Sorry, I thought the problem was using Spring and Hibernate to produce the list of translated enum values, so I suggested the view to make it easier for the ORM to handle (since it queries against a single view instead of figuring out all of the joins on its own). But... it looks like I misunderstood what you were asking. – FrustratedWithFormsDesigner Mar 8 '13 at 22:04

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.