patternModerate
Modify the column names of database tables to fit my app's technical limitations?
Viewed 0 times
tablesthecolumnappnamesdatabasetechnicallimitationsfitmodify
Problem
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?
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?
Solution
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.
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.
Context
StackExchange Database Administrators Q#185610, answer score: 10
Revisions (0)
No revisions yet.