CODEWITHSUNDEEP
javasqldatabase-designmybatis
user2022068
user2022068

Reputation:

Names for database tables like t1,t2,t3 etc

We have the following situation - we have framework with which different developers can work and who don't know about other developers. And we have a customer who can take extensions (ebj osgi bundles and sql scripts) from different developers and add them to one database.

So as there are different developers there can be name conflicts (table, index, foreign keys). We would like to implement java like name - for example: comOrganizationFooTable however the maximum length of table name and columns is 30 characters. Besides it is CRUD application and there are tons of joins with tables that can be from different developers. And the last - we use mybatis with xml mappers.

Taking all this into consideration I think about the following solution. Customer adds new tables to database via special program that reads all long database names, replaces them to tN, creates tables tN,tM... and in special tables saves the links (t2 -> table original name). After that when ejb osgi bundle starts program replaces original names in xml mapper with tX and loads this updated xml mapper to mybatis configuration.

The most important thing is that long unique original names exist both in both sections of xml mapper: in mapper section and sql code section. It's very important when we join different tables and want to use mapper sections from different xml mappers.

Example:

<resultMap id="readItemsRM" type="com.mycompany.product.SomeDTO">
        <id property="id" column="%comMyCompanyProductSomeTable.id"/>
        <result property="name" column="%comMyCompanyProductSomeTable.name"/>
</resultMap>

 <sql id="mainSelect">
   SELECT 
     %comMyCompanyProductSomeTable.id,
     %comMyCompanyProductSomeTable.name
   FROM
     %comMyCompanyProductSomeTable
 </sql>

So, after dynamic changes all %comMyCompanyProductSomeTable becomes for example t29.

Following this methos we don't need to use any AS. All references across databases are unique and we don't exceed the maximum table and column name limits.

The question - how do you think, is it good or bad solution and what are other solutions of such problem?

Upvotes: 1

Views: 498

Answers (1)

Neil McGuigan
Neil McGuigan

Reputation: 48246

The solution to your problem is to use namespaces. That's what namespaces are for. In database-land, a namespace is called a schema. MySQL decided to be weird and call their schemas 'databases'

Normal db server:

Database Cluster
   Databases
     Schemas
       Tables, etc

MySQL db server:

Database Cluster
     Schemas (called 'databases')
       Tables, etc

Force developerX to use developerX schema. If he makes a table kittens, it must be called developerX.kittens

Yes, you can have foreign keys between schemas. Use the fully qualified name in your foreign key: schema_name.table_name.

Anyways, if you don't want to do that, then come up with a short code prefix for your developer companies, such as "x3" for "Company 12", and force them to prefix table names with their prefix code, such as x3_kittens. This is no less work than schemas, and is hackish.

Upvotes: 2

Related Questions