Hi, I am looking for a new data modeling tool.  I was using DBDesigner4.  I have a requirement that (at least) I don't think most modeling tools support.  Basically, we have two databases.  One database contains all the common tables such as customers and accounts.  The other database is more application specific but the tables have foreign key constraints into the common tables.

In DBDesigner4, I can actually one database diagram representing the common tables.  Then create another separate diagram containing tables from the 2nd database.  On top of that, I can link in the first database diagram so that I can create database constraints from a table in the 2nd database to the a table in the 1st database.  When I generate Create SQL script from the 2nd diagram, the tables from the 1st diagram won't appear which is correct.

I tried out the community version but it doesn't seem to support this feature.  Just want to confirm.

Thanks,

Ben

I think you could use ModelRight to do that.  ModelRight has Model Subsets that you could use to create separate diagrams for your two databases.  To Reverse Engineer them, you would RE one database, and then use the Compare with Database feature to add the tables from your second database into an empty Model Subset.  The tables would exist within the same model, but the Model Subsets would categorize them for the purposes of schema gen and compare with database.  You could have a third Model Subset that shows all of the tables together - so you could create your cross database relations.  You could mark those relations as not generated - so they wouldnt get added to your schema gen.  Let me know if that works for you.

---

Scott Reynolds
Senior Technical Support
ModelRight, Inc.

 

Hi Ben,

 Jus a quick question... when you say database, do you mean server? In MySQL the terms are a bit confusing, since a server is made up from several databases (which in other RDBMS are known as schemas or catalogs, etc while database usually means server). If you do mean database in MySQL sense, then ModelRight supports that with no problem... when you do Reverse Engineer you should see all the databases from the server you are connected to listed on the second page on the wizard; all you need to do is just to select the databases you want too be included in your model. However, if you want to pick databases from different servers then you will have to use Database Compare as described above.

Hope this helps. 

 

---

ModelRight Evangelist

The databases I am referring to is in the same MySQL database server.  However, let me clarify what I am trying to achieve.  There are currently two databases - lets call them common and app1.  I like to keep the schema for these two databases separate.  For example, if I want to add a table to the common schema, I load up the common schema model / project and add a table. 

However, some of app1 tables have referential integrity to some of the tables in common - so I like to have the ability to show some of the common tables in the app1 schema but not have the scripts generate the create tables for them (just the foregin key constraint to them).  It is kind of like a link to another schema / model functionality - which DBDesigner4 allows me to do.

Now why don't I want both schemas in the same project?  Because of maintainability and modularity.  I only have two databases/schemas right now, but we will have more coming online.  Almost all of them will be referencing the common tables.  I don't want six different database schemas in one project.  It will get hairy fast.

I hope I am more clear this time around.  If not, let me know.

Thanks for all your help in advance,

Ben

Hi Ben,

It sounds to me that the easiest way would be to reverse engineer both databases common and common1 in your model and create two model subsets, one for each database and include in each model subsets all the objects of the associated database. ModelRight will allow you to generate the SQL statements for each model subset separately (see the options on second page of the Generate to Database wizard – filter by Model Subset). Note that, you can generate schemas for each database separately anyway (even if you do not split the model in separate model subsets) using the filter by database option (on the same wizard page). In fact you will probably want to use both options (filter by database and model subset).  Also, there is an option in Model Explorer to show up only the tables/views from the current model subset (that way, you will see objects only from a database in the current diagram and Model Explorer).

Best Regards

 

 

---

ModelRight Evangelist

But if I understand you correctly, I will have two copies of the common schema.  And if I make a change (e.g. add a table or a column) in the common schema, I will have to update all copies of it.

Is this a feature (allowing one model to link to another model) you guys will consider for a future release?

Thanks,

Ben

No, there is only one copy of any object. Basically, what I was saying is that your model should contain both databases and you slice the model in two, a slice (i.e. Model Subset) for each database. The Model Subsets are just "views" of the same model... if you have a table A in two Model Subsets M1 and M2, then if you change the table A in M1 it will automatically be changed in M2 too. Basically, Model Subsets is just a way to structure your model in smaller chunks, for simplicity; using a model subset you narrow your model view (to a schema for instance, in your case), you can use it to generate SQL scripts for that model subset, etc...There are some on-line demos on the website that can give you a better understanding of how Model Subsets work.

Why don't you try to use Model Subsets as described in one of my previous posts and see how it works and then see what questions remain answered.

---

ModelRight Evangelist

Yes, I tried your suggestion but it doesn't 100% solve what I am trying to accomplish (at least as far as I understand).

The model subset does allow me to show two databases/schemas and link them together in one model (what you guys are calling a model) - and the ability to generate SQL for one or all of the model subsets.  Not bad...

However, if I have to create a third schema that also relies on the common schema (the first submodel), I will have to stick this schema into the same model - meaning three model subsets - even though the second and third model have no relevance or relation to each other.  Here is an example:

Model 1 - Common Model

Customer

Customer Address

Products

Model 2 - Accounting

Invoice

Invoice History

Model 3 - Sales

Order

Order Details

Order History

Both Accounting and Sales also reference/use the Common tables, but have no relation to each other.  Putting all three models into one "model" in ModelRight doesn't make sense.  If I have six applications, I have to load one model that contains all six model subsets.  Bad idea.  Not to mention different development team manages their own application and schema.  Ideally, all three models should be created as separate "models" files.  Then have the ability to link them into each other as model subsets.

Sorry, it sounds like I am being picky.

Let's just say I haven't found a ER tool that does this - except for DBDesigner4.  I am trying to find a new tool because it is no longer supported and it is getting to a point where it is getting rather buggy for me.

Thanks,

Ben

 

Ok. Now I understand better your use case. We do not support directly what you want.

1.       We do plan to support exactly what you want via what we call library models (basically models that can get included in other models). So, your common schema will be a library model. Look for this feature in one of our next releases.

2.       As of now, you can use Model Compare to do most of what you want. You can break your schemas in two models  (M1 containing (Common + Accounting) and M2 containing (Common + Sales)) and then using Model Compare to synch the Common schema between M1 and M2 (basically, if you make a change in M1 in the Common part then you can use Model Compare to export that change into M2 or vice versa). You can also keep your Common schema in its own model (the “base” model…) and have that model the only place where modifications are made (as far as the Common part goes) and then use Model Compare to push any changes from Common into M1 or M2.

---

ModelRight Evangelist