3

I work in a company which works on gigantic (let's say hundreds of gigabytes) databases delivered by 3rd parties. Further, I have to create a data base connection from within a software (that is, an application program) used by the company to load and visualize the data.

The problem

The database has column names character lengths of 32+, but the software app only supports column names lengths of up to 31. So these tables containing the columns can not be loaded, and I can not really work with the database.

The questions

Should I touch the database and shorten the column names everytime it is necessary (for updates etc..) or should I rather push the company to use software which supports longer column names ?

On the one hand, I think it is very ugly to touch the raw data, on the other hand the app licenses the company already bought were very expensive and just switching the sotware might not be such a popular move...

Or does it depend on the particular case? Would the number of affected columns or the price of the software correlate in any way with the decision I would have to make?

Or is there an even better solution for this?

  • 1
    I'm not sure I understand: is the problem that the column names are too long for whatever visualizing software you are using? If so, yes, renaming the columns in the database seems like a workable idea. If the actual data is too long, you have a much more serious issue. – BradC Sep 11 '17 at 13:50
  • Only the column names are too long, but with them of course certain constraints etc. .. I always feel a little guilty or unprofessional when I just rename those column names because I think this is not a nice way to handle this. But you think this is legit? – Flugmango Sep 11 '17 at 14:09
  • 3
    Sure, sometimes you gotta do what you gotta do. Lots of practical questions, though: manual renaming or scripted? Will chopping them off at 31 work? Or would that leave duplicate names? How often does a new db come in that needs this attention? How quickly does it need to be ready to go? Does it have to be repeatable (the next time that same db comes in, does it have to be renamed in the exact same way)? Not questions you have to answer for me, but questions that you should answer for your own work, and may help you decide whether its possible/practical to script, or just to do it by hand. – BradC Sep 11 '17 at 14:16
  • Rule no. 1 Do no evil! - always make sure that you can get back to square one by keeping a backup of the original untouched data! After that, I think that @JohnEisbrener 's idea of using VIEWs is very elegant - what RDBMS are you using? Forgot to add, what on earth reporting software are you using - I would bin it ASAP - column length < 32 is a ludicrous requirement in this day and age! – Vérace Sep 11 '17 at 14:48
10

Build Views on top of the tables. Every RDBMS should have this functionality as VIEWS are part of the SQL Standard. A View will allow you to call the columns whatever you so choose without having to change the underlying structure provided by the 3rd parties you say are providing the databases. Views may also allow you some flexibility to standardize names and maybe even data types to your liking. Finally, if you get additional data from the 3rd parties that can easily be loaded into the current structures, this will minimize any rework needed for future ETL processes.

  • Good idea. Once you've created views for a particular data structure, it would be trivial to recreate those views in the new copy of the client's database. Easily scriptable and repeatable. Only problem I can even imagine is if whatever weird reporting tool they're using doesn't support views (not implausible, if it doesn't even support moderate-length column names). – BradC Sep 11 '17 at 14:38

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.