Problem with table names

[kklepper]

Everything works like a breeze now -- except for tables with unconventional table names.

I used to name cross tables like a_x_b, but this convention is both cumbersome and hard to read and understand. Very easily you get constrictions like a_new_x_b_old which is just a bad mess.

So I looked for a better naming convention; in principle every letter used for filenames can be chosen except those with special meaning like .. I opted for ~, and indeed I liked the notion a_new~b_old much better.

Now MySQL has problems with table names like this, so they invented ticks to circumvent that problem. And if I understand correctly the general advice is to enclose all table names, database names, column names etc. with ticks. Once done, everything works as it should.

Now with Mogwai ERDesignerNG, there seems to be a problem. This is the stack trace:

java.util.concurrent.ExecutionException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '~lfaculty' at line 1

etc.

What can I do to get around this problem?

[mirkosertic]

Quoted DB object names are always cumbersome. By quoting table names, database defaults like case sensitivity on object names are overridden, as specified by SQL standard. Quoting only unconventional table names doesn't make life easier, as we have to remenber which tables need quotation, which is ultimately definied by the DDL which was used creating it.

I tried to get around such problems in the past. Maybe ERDesigner needs some dialect enhancements for this, but I've to test.

Do you have an example schame as DDL as a testcase?

[kklepper]

You are right. I try to find a better solution. It was just an idea.

My goal with this was to make the structure of the database more intuitively understandable.

Let me explain my motivation:

I work with that main database of mine with some 70 tables for years. Now having assembled some more gigabytes of data ready to be integrated into that database, I just wanted to get a fresh idea of the structure of this main database to be able to distribute this data correctly, because in this process of gathering the data I've broadened my vision of the structure of this data. Consequently I most probably have to change parts of my original design in the process.

First I noticed that my initial naming convention was not that intelligent, so I began with that problem, as indicated. Of course, I had to reflect this renaming in my code as well, and it looks like everything went well at first attempt, much to my surprise.

Next I wanted to get a visual representation of the relations of these tables in order to understand the problem of distribution of this as yet only poorly organized new data better. To this end, I looked for tools to help me. Among those I found so far, MySQL Workbench and SQL Power Architect looked the most promising, but none really convinced me yet.

Actually I only need a rough sketch, so in the meantime I tried to use a graphics program and next WinWord, but this wasn't what I hoped for either. Finally I remembered a mind map program I used to illustrate some ideas years ago and I think I'll give it a shot as well. This graphical representation I look for is not the goal of my work, it is only a means to get my ideas right.

Your program aims at totally different goals, so it is definitely overkill for me.

By the way, the tick convention is supported by MySQL Workbench (no wonder), but also by Adminer, which I use a lot, and most probably by any other tool supporting MySQL.