0

Problem: I have a 30~ish tables database with all tables/columns in English. I'm thinking about creating views to make it all readable in another language, that is, 1 view for each table with alias for all columns.

Reason: One of my bosses (the one that knows how to use a database) didn't like to know that the database was all in English but he only spoke when it was too late and too expensive to rebuild. Now the software will have a Administrative Tool for extracting PDF reports with pure SQL. You type the SQL and get a PDF with the result.

Cost: I was researching views and they seem expensive, this question in particular got me thinking it's not a good idea to do it.

Community
  • 1
  • 1
Marco Aurélio Deleu
  • 4,279
  • 4
  • 35
  • 63
  • I'm not sure I'd do this. I think it's going to be a real headache to maintain a database in two languages - even if one set of tables is really a view of another set of tables. If it was me, I'd use the presentation layer/application-level code (assuming you have one) to handle issues of locale. – Strawberry Nov 05 '14 at 13:20

1 Answers1

2

In this case, a better reference for view performance is the documentation itself. There are two methods that MySQL uses for handling views, one called "merge" and the other "temptable". Most of the performance issues with views are due to "temptable",

There are significant and important limits on the use merge. But, happily for you, renaming columns is not among them. So, a view that simply renames columns can use "merge":

create view v_table as
     select col1 as col_1, col2 as col_2
     from table t;

So, you can write a separate view for each table, renaming the columns and you should not have to worry about performance. You do have to worry about maintenance, but that is another issue entirely.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Your create view statement is pure as oppose to the documentation itself. If I understood it right, I should define the algorithm as merge, right? – Marco Aurélio Deleu Nov 05 '14 at 13:13
  • @MarcoAurélioDeleu . . . I would be explicit in defining the algorithm as "merge". And then check to be sure that "merge" is really being used. – Gordon Linoff Nov 05 '14 at 13:16