MySQL Bugs: #56491: Foreign Key naming incompatibility

Bug #56491	Foreign Key naming incompatibility
Submitted:	2 Sep 2010 10:15	Modified:	2 Sep 2010 11:14
Reporter:	Mutlu Tevfik Kocak	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S4 (Feature request)
Version:	5.2.26	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	cakephp, foreign, key, model, name

Description:
The naming conventions for CakePHP, require that all table names be plural and all foreign keys has to be singular.

In example: CakePHP automatically recognize FK's and Table's named as:

STable: user_id, Foreign Key to STable from DTable: user_id

Problem:

Foreign key name generation has many tokens at Mysql Workbench at Model Options, Foreign/Key Relationship defaults like dtable, stable, table, column, upper, capitalize etc. However this tokens does not permit to delete last s of plurality from table name to generate foreign key name singular.

Due to this issue, changing any table name manually, takes too much time and any script to convert existing foreign keys have risk of distort correct foreign keys.

How to repeat:
After opening a file, creating a new foreign key.

Tried too many coices, but no result at Model -> Model Options -> Foreign Key / Relationship Defaults.

Suggested fix:
Most basic solution would be nice if:
To add an extra option to stable, dtable, upper, lower; substr(x,y), where x would be initial char number, y last char number.

Most advanced solution would using Regular Expressions to replace and generate Foreign Key names.

Thanks,
Mutlu Tevfik Kocak
mtkocak@gmail.com

Sorry for error at:

Wrong:
STable: user_id, Foreign Key to STable from DTable: user_id

Right:
STable name: user
Foreign Key name to STable from DTable: user_id

I realized that it is more feature request than S3.

Also sorry about that.

Mutlu

I am too attentionless:

Wrong:
STable name: user
Foreign Key name to STable from DTable: user_id

Right:
STable name: users
Foreign Key name to STable from DTable: user_id

Thank you for the feature request.

substr(x,y) or token[x:y] (as in python)

Mutlu,

I understand the rationale, but dealing with plurals is far more complex than you assume. How would you handle these?

"entries" --> "entry"
"axes" --> "axis"
"children" --> "child"

This is why, CakePHP aside, most tooling demands a convention of using the singular exclusively (e.g., naming a table "entry" instead of "entries") unless you manually handle the plurals.