Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I thought that previously when I reverse engineered databases, mySql Workbench figured out the relationships from the id names and connected the tables leading to an awesome ERD, even when the foreign key constraint clauses don't exist and they are just used through the fields (xxxx_id) have the foreign key values.

However currently, when I pick various databases (I create them in rails) and try to import them I end up with this nasty looking smodge:

enter image description here

Instead of:

enter image description here

share|improve this question
    
MySQL Workbench figures out the relationships when they exist, as FOREIGN KEY constraints. –  ypercube Jun 22 '12 at 13:49
    
When no relationships exist, it doesn't guess anything. Note that even a human would only guess that survey_sections.survey_header_id is an FK to survey_headers (id). And he could be wrong. –  ypercube Jun 22 '12 at 14:05
    
Some people add an _id to an FK column. Some use id, others use tablename_id, others use tablenameID, etc... as a PK name. The list is endless. And what about compound PK and FKs? What if the constraint should be towards a UNIQUE and not towards a PRIMARY key? How should WOrkbench (or anyone) guess? –  ypercube Jun 22 '12 at 14:07
    
Sure, it seems like a few dialog boxes or settings could address the most common scenarios of othertableID othertable_ID, etc. and no compound keys used for joins. The possibilities are endless sure, but the most common ones are out there. –  Michael Durrant Jun 22 '12 at 14:13
1  
What could be done is a script to create those FKs (based on these specs) and then let Workbench reverse engineer. –  ypercube Jun 22 '12 at 14:34
show 3 more comments

2 Answers

up vote 4 down vote accepted

MySQL Workbench does not have the capability to "infer" the relationships from table column names, or MyISAM tables. It requires that the constraints be defined in the database or SQL script being reverse engineered.

share|improve this answer
add comment

For any rails coder who finds this, you can use the immigrant gem. This generates a migration that builds all missing foreign key constraints. It uses foreigner, which provides helper methods for foreign key constraints. Then workbench should be able to build the relationships.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.