Database Modeling With Diagrams - CCModeler
Published:
Browse All Articles > Database Modeling With Diagrams - CCModeler
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
DatabaseModeling by
Table of Contents
1 About CCModeler
1.1 Features of CCModeler
2 How to use
2.1 Entering an own model
2.2 Import Access database
2.3 Import SQL Server database
2.4 Export erviz
2.5 Export yEd
2.6 Export ADaMo
2.7 Deleting a database model
2.8 Adding further/changing existing colors
3 Further ideas
3.1 Tweaking the export
3.2 Use it for auto documenting purposes
I do not want to explain all the backgrounds about different ways of creating such a model, the different notations which exists or theories about them – there are a lot of examples about that in the Internet so this should not be another attempt to explain that. Here are some examples if you want to read about:
https://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model
http://www.codeproject.com/Articles/878359/Data-modelling-using-ERD-with-Crow-Foot-Notation
If you work with software like Microsoft Access or Microsoft SQL Server Management Studio you'll see that the tools they offer to create diagrams and structure your work are not very good. Access has only one relationship editor which must work with all existent tables in the same database to create relationships between them, and it duplicates the table with a new name (although only one physical table exists) to create independent relationships. That's OK for up to let's say 20 tables but then it gets very chaotic. Moreover, you are working with the real tables and not with the pure design.
SSMS (SQL Server Management Studio) is a little bit better as it allows to create as many diagrams as you want with an infinite size and also allows to add annotations. That makes the work more structured, but you also directly work with the real tables and relationships and some bugs here makes it a tool where you need to work very carefully to not destroy your existent tables or your diagram if you work with the tables without opening the diagram(s) where it is in.
So both have graphic features but are not really good for complex designs.
There are a lot of good tools out there to help you with these problems, one of it of course the "good old" Visio which you can get with a special database developer extension, it allows you to import existing database structures, work with them as graphics but also as real entities and which is able to write them back as real entities on the database. But it's also a tool which costs a lot and also not really easy to use (my personal opinion of course). In the design phase you do not want to work with real structures, you want an easy to use graphic tool where you can concentrate on the database design and not on how to position, size and connect the graphic objects.
After searching a while I personally found the following tool a LOT easier to use (and it also can be used for a lot other purposes, not only database design):
yEd
It is totally free and has no hidden advertisements or such, it's purpose is to demonstrate the capabilities of a graphic library the offerer sells, but it's not a demo program, it's a really good graphic tool and the easiest tool I know to draw diagrams of each sort.
The tool I want to show here is a simple MS Access database with open source code which can be used to easily prepare a graphical diagram like with yEd, the CCModeler. The purpose of this tool is not to create a ready database but to have a simple way of entering all the text input which you can of course also directly enter into yEd but it's more work than to enter all entities and some of the most important attributes into a single database file. Moreover, defining primary and foreign keys and the relations between the tables automatically creates the relationship lines in the yEd drawing in crow foot notation (explanations see links at the beginning of this article).
But besides yEd there are other tools which are also supported by this little tool. Some people like to use another freeware tool called "erviz" which is able to create a diagram graphic using a text file which describes the model as text and then creates a graphviz diagram out of it. I personally think that this is a lot too much commandline work, but some people like that so I made an exporter for that, too. Explanations about it's usage can also be found on the erviz website.
The third tool which is supported by CCModeler is "ADaMo".
The purpose of ADaMo is to use a list of entities and then ask some questions about how they should work with each other. This is especially made for beginners which starts to create data models for databases. CCModeler can export the entities as input to ADaMo and ADaMo can create a ready Access database file from the inputs of the user.
That's very simple. First you need to create a new database model entry. You'll see three subforms contained in one main form, each of them has a record navigator at it's bottom. The one for the main form is the one at the bottom of the complete form. Klick the icon for "new record" (arrow with yellow star), then enter a database name at the top and optionally a comment.
Next you need to define the entities, so for example you create an entity for "PurchaseOrder" and another one "PurchaseOrderPositions" to have the well-known master/slave table relationship defined. Choose a color if you want for each. Now your table entities are defined and you can design some attributes for them.
After selecting an entity (click on a row of the entity list) you find all attributes of this entity in the attributes subform at the right side. Choose an existing attribute from the list if one of it fits your needs or define an own one by adding it to the attributes list. If you use at least Access 2007 you'll find a transparent icon at the dropdown list of the attributes combobox which is used to open the attributes form. Here you can enter as many new attributes as you want (each attribute name can exist only once in this list). After closing this form it is available in the attributes combobox. If you use A2003 you must open the attributes form on your own (frmEntitiesAttributes), as far as I remember there is no automatic opening of the form like with in A2007.
Now you can define if the attribute (table field) is used as primary key, foreign key or computed column. Any combination is possible.
As last step you need to define the relationships between your tables (entities). This can be done with the relationships subform at the bottom. Here you simply select one entity at the left side, one at the right and a relationship type from the combobox in the middle. Each relationship between two tables can only exist once. As this is used to design a relationship between entities and not fields (like in the physical database model) it doesn't matter how many fields will be in the relationship later, the diagram will only show the name of the entity with it's attributes as list, and then a relationship line between two entities in the way designed in the relationship type here.
For the same reason no datatype is used in CCModeler to concentrate on the design and not the physical model.
That's it, your model is ready to be exported as diagram.
Often you already have an existing database, maybe you got a database from a former developer and you should improve it. So you need an overview over the chaos ( ;-) ) and the relationship window only shows a ton of tables and a net of relationship lines which is unreadable.
Simply import it with the button "Import From Access". All you need to do is to copy the Access database file into the folder of CCModeler (the backend file if you have a frontend/backend solution), click the button and enter the name of the file without path and with file extension like "YourDatabaseName.accdb" (or ".mdb"). The file name is used to automatically create a new entry in the database model table, if it was already imported before the module doesn't allow to import it (change the name of the existing or delete the existing model, see below for more information).
Then the database file will be analyzed to find all tables and their relationships between them and also primary and foreign keys. That will be used to create all entities, attributes and also the relationships.
That's it, your model is ready to be exported as diagram.
It's as easy as with the Access database, you only don't need to copy a file. Simply start the importer and enter a connection string which have at least read access to your database and system tables, then all tables together with their schema names will be imported into CCModeler, the rest is identical to the Access importer. As SQL Server can have more complex relationships as Access and DAO is not able to fill it's relationship collections the relationships are tried to be extracted from the result of the system views so "normal" relationships should be imported without problems, more complicated may not be imported correctly.
The connection string need to be in a format that Access is able to read it with DAO, here's an example:
That's it, your model is ready to be exported as diagram.
Simply click on "Create erviz input file" and then you'll be asked to enter a name which is used as title in erviz and also as filename. The default is the name you entered as database model name. The file will be created in the folder of CCModeler.
The created txt file can then be used to create the graphviz file using erviz. Erviz contains a HTML documentation after you've downloaded it there you can find everything you need to create a diagram with it.
Clicking on the "Create yEd graphml" file directly creates (and overwrites existing!) the graphml file yEd can directly use as input file. So for adjusting the model with yEd copy it to another folder before to avoid overwriting it with your next export click.
After you've opened the file with yEd you'll find a strange looking diagram where all entities and a lot of line are all positioned in the center of the screen. That's because it would be a lot work to do all this layout work programmatically with VBA although it would be possible. But that's not needed as yEd can do better. Simply use the "Layout" menu of yEd which offers a lot of possible methods to layout the diagram automatically, i.e. choose the hierarchic model and let the defaults in the options window and click OK, now you have a perfectly designed diagram and if that's not what you need, adjust the lot of options or choose another layout type with again a lot of options, one of them will surely fit your needs.
As ADaMo can only work with one database model at the same time CCModeler deletes all entries in the ADaMo database. So copy the original file to the CCModeler folder and click "Export to ADaMo", then it will immediately export the database model from CCModeler to it. For further explanations on how to use ADaMo see Andreas Stern's website (see the link above).
The main form of CCModeler is locked for deleting to not accidentally delete a complete model. So if you want to delete a database model, directly open the table "tblDatabase", go to the database model you want and delete the row. All entities, attributes and relationships will then be deleted also.
There are a few colors defined in the table "tblEntitiesColors". Some of them (except "Pink") will also be used as colors in ervin, but there as color names only, you cannot define the color. The columns "F_Color1" and "F_Color2" are used as gradient colors for yEd, you can change them like you want or add new ones. The simplest way to do that is to design the colors in yEd where you can find the hex color string in the properties window.
CCModeler is open to be changed by you to fit your needs. As you can see in the code module of frmMain it is not very hard to write an own exporter. So if you maybe want to create a graphviz export directly you can write your own exporter.
For that purpose two tables exists to make that easier: tblExportSettings and tblExportSettingTypes. The last one currently consists of one record only for graphml, add a row for each new export type (name doesn't matter).
The settings table consists of a foreign key for the export type to group the settings in a query in the exporter. The "F_ExportSettingValue" column contains the export text and some variables in the form "{variablename}" so in the exporter you only need a "Replace" function to replace variable names in curly brackets with the desired value. The "F_ExportSettingName" column contains a free to choose name for the export snippet. As yEd uses a simple XML structure it is easy to define a header and footer, then one for each entity (node in yEd) and one for each line between entities (called "edge" in yEd).
So if you have any other output format then add the desired settings here so you can simply import them using a recordset in VBA and only replace the variable names with the desired values as you can see with the yEd exporter. That keeps the code easy to read and if you need to change the output settings you don't need to change the code, simply change the setting value in this table.
The code/tables of CCModeler can of course be used to auto create documentation grahics in own Access databases. Simply import any needed table/form/code module into your own database file and tweak the VBA code a little bit so you don't need to use the frmMain to import and export, just use the own database filename and a defined export name to create the output. In case of erviz you could also add a Shell command to the VBA code to directly create the diagram.
As you can also add comments to the relationships in CCModeler you can also add further export settings to add them as comments to yEd edges and so on.
Maybe this little tool helps to make diagrams faster than before, free for all purposes.
Have fun in experimenting with it
Christian
1 About CCModeler
1.1 Features of CCModeler
2 How to use
2.1 Entering an own model
2.2 Import Access database
2.3 Import SQL Server database
2.4 Export erviz
2.5 Export yEd
2.6 Export ADaMo
2.7 Deleting a database model
2.8 Adding further/changing existing colors
3 Further ideas
3.1 Tweaking the export
3.2 Use it for auto documenting purposes
1 About CCModeler
When you get the order to create a database for any business requirement one of the first things to do after collecting all the business needs the database should fullfil is to draw a model of the database entities you need to create and which relationships and –types exists between them.I do not want to explain all the backgrounds about different ways of creating such a model, the different notations which exists or theories about them – there are a lot of examples about that in the Internet so this should not be another attempt to explain that. Here are some examples if you want to read about:
https://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model
http://www.codeproject.com/Articles/878359/Data-modelling-using-ERD-with-Crow-Foot-Notation
If you work with software like Microsoft Access or Microsoft SQL Server Management Studio you'll see that the tools they offer to create diagrams and structure your work are not very good. Access has only one relationship editor which must work with all existent tables in the same database to create relationships between them, and it duplicates the table with a new name (although only one physical table exists) to create independent relationships. That's OK for up to let's say 20 tables but then it gets very chaotic. Moreover, you are working with the real tables and not with the pure design.
SSMS (SQL Server Management Studio) is a little bit better as it allows to create as many diagrams as you want with an infinite size and also allows to add annotations. That makes the work more structured, but you also directly work with the real tables and relationships and some bugs here makes it a tool where you need to work very carefully to not destroy your existent tables or your diagram if you work with the tables without opening the diagram(s) where it is in.
So both have graphic features but are not really good for complex designs.
There are a lot of good tools out there to help you with these problems, one of it of course the "good old" Visio which you can get with a special database developer extension, it allows you to import existing database structures, work with them as graphics but also as real entities and which is able to write them back as real entities on the database. But it's also a tool which costs a lot and also not really easy to use (my personal opinion of course). In the design phase you do not want to work with real structures, you want an easy to use graphic tool where you can concentrate on the database design and not on how to position, size and connect the graphic objects.
After searching a while I personally found the following tool a LOT easier to use (and it also can be used for a lot other purposes, not only database design):
yEd
It is totally free and has no hidden advertisements or such, it's purpose is to demonstrate the capabilities of a graphic library the offerer sells, but it's not a demo program, it's a really good graphic tool and the easiest tool I know to draw diagrams of each sort.
The tool I want to show here is a simple MS Access database with open source code which can be used to easily prepare a graphical diagram like with yEd, the CCModeler. The purpose of this tool is not to create a ready database but to have a simple way of entering all the text input which you can of course also directly enter into yEd but it's more work than to enter all entities and some of the most important attributes into a single database file. Moreover, defining primary and foreign keys and the relations between the tables automatically creates the relationship lines in the yEd drawing in crow foot notation (explanations see links at the beginning of this article).
But besides yEd there are other tools which are also supported by this little tool. Some people like to use another freeware tool called "erviz" which is able to create a diagram graphic using a text file which describes the model as text and then creates a graphviz diagram out of it. I personally think that this is a lot too much commandline work, but some people like that so I made an exporter for that, too. Explanations about it's usage can also be found on the erviz website.
The third tool which is supported by CCModeler is "ADaMo".
The purpose of ADaMo is to use a list of entities and then ask some questions about how they should work with each other. This is especially made for beginners which starts to create data models for databases. CCModeler can export the entities as input to ADaMo and ADaMo can create a ready Access database file from the inputs of the user.
1.1 Features of CCModeler
- Works with any number of database projects
- Each database can consist of any number of entities which can also have a single color (for output in erviz and yEd)
- Each entity can get an independent number of attributes (fields), where each can be chosen from a unique list of attributes (makes it easier for new databases so you can reuse earlier defined attributes)
- Each attribute can be defined as primary key, foreign key or computed column (last one not used other than to mark it as such)
- The entities can be defined in 32 different kind of relationships, the list is taken from the documentation of erviz where you can find further explanations about relationships.
- Two import modules allows to import either an existing Access database table structure or an existing SQL Server structure so if you want to get a graphic overview about an existing database you can use that to import the existing structure and export it.
- Three modules to export the created or imported structure either as yEd graphic, as erviz input file or as ADaMo (V22) input to create a real Access database from the structure.
2 How to use
2.1 Entering an own model
That's very simple. First you need to create a new database model entry. You'll see three subforms contained in one main form, each of them has a record navigator at it's bottom. The one for the main form is the one at the bottom of the complete form. Klick the icon for "new record" (arrow with yellow star), then enter a database name at the top and optionally a comment.
Next you need to define the entities, so for example you create an entity for "PurchaseOrder" and another one "PurchaseOrderPositions" to have the well-known master/slave table relationship defined. Choose a color if you want for each. Now your table entities are defined and you can design some attributes for them.
After selecting an entity (click on a row of the entity list) you find all attributes of this entity in the attributes subform at the right side. Choose an existing attribute from the list if one of it fits your needs or define an own one by adding it to the attributes list. If you use at least Access 2007 you'll find a transparent icon at the dropdown list of the attributes combobox which is used to open the attributes form. Here you can enter as many new attributes as you want (each attribute name can exist only once in this list). After closing this form it is available in the attributes combobox. If you use A2003 you must open the attributes form on your own (frmEntitiesAttributes), as far as I remember there is no automatic opening of the form like with in A2007.
Now you can define if the attribute (table field) is used as primary key, foreign key or computed column. Any combination is possible.
As last step you need to define the relationships between your tables (entities). This can be done with the relationships subform at the bottom. Here you simply select one entity at the left side, one at the right and a relationship type from the combobox in the middle. Each relationship between two tables can only exist once. As this is used to design a relationship between entities and not fields (like in the physical database model) it doesn't matter how many fields will be in the relationship later, the diagram will only show the name of the entity with it's attributes as list, and then a relationship line between two entities in the way designed in the relationship type here.
For the same reason no datatype is used in CCModeler to concentrate on the design and not the physical model.
That's it, your model is ready to be exported as diagram.
2.2 Import Access database
Often you already have an existing database, maybe you got a database from a former developer and you should improve it. So you need an overview over the chaos ( ;-) ) and the relationship window only shows a ton of tables and a net of relationship lines which is unreadable.
Simply import it with the button "Import From Access". All you need to do is to copy the Access database file into the folder of CCModeler (the backend file if you have a frontend/backend solution), click the button and enter the name of the file without path and with file extension like "YourDatabaseName.accdb" (or ".mdb"). The file name is used to automatically create a new entry in the database model table, if it was already imported before the module doesn't allow to import it (change the name of the existing or delete the existing model, see below for more information).
Then the database file will be analyzed to find all tables and their relationships between them and also primary and foreign keys. That will be used to create all entities, attributes and also the relationships.
That's it, your model is ready to be exported as diagram.
2.3 Import SQL Server database
It's as easy as with the Access database, you only don't need to copy a file. Simply start the importer and enter a connection string which have at least read access to your database and system tables, then all tables together with their schema names will be imported into CCModeler, the rest is identical to the Access importer. As SQL Server can have more complex relationships as Access and DAO is not able to fill it's relationship collections the relationships are tried to be extracted from the result of the system views so "normal" relationships should be imported without problems, more complicated may not be imported correctly.
The connection string need to be in a format that Access is able to read it with DAO, here's an example:
ODBC;DRIVER=SQL Server Native Client 10.0; UID=WindowsUserName; DATABASE=AdventureWorks; Trusted_Connection=Yes; SERVER=Servername
That's it, your model is ready to be exported as diagram.
2.4 Export erviz
Simply click on "Create erviz input file" and then you'll be asked to enter a name which is used as title in erviz and also as filename. The default is the name you entered as database model name. The file will be created in the folder of CCModeler.
The created txt file can then be used to create the graphviz file using erviz. Erviz contains a HTML documentation after you've downloaded it there you can find everything you need to create a diagram with it.
2.5 Export yEd
Clicking on the "Create yEd graphml" file directly creates (and overwrites existing!) the graphml file yEd can directly use as input file. So for adjusting the model with yEd copy it to another folder before to avoid overwriting it with your next export click.
After you've opened the file with yEd you'll find a strange looking diagram where all entities and a lot of line are all positioned in the center of the screen. That's because it would be a lot work to do all this layout work programmatically with VBA although it would be possible. But that's not needed as yEd can do better. Simply use the "Layout" menu of yEd which offers a lot of possible methods to layout the diagram automatically, i.e. choose the hierarchic model and let the defaults in the options window and click OK, now you have a perfectly designed diagram and if that's not what you need, adjust the lot of options or choose another layout type with again a lot of options, one of them will surely fit your needs.
2.6 Export ADaMo
As ADaMo can only work with one database model at the same time CCModeler deletes all entries in the ADaMo database. So copy the original file to the CCModeler folder and click "Export to ADaMo", then it will immediately export the database model from CCModeler to it. For further explanations on how to use ADaMo see Andreas Stern's website (see the link above).
2.7 Deleting a database model
The main form of CCModeler is locked for deleting to not accidentally delete a complete model. So if you want to delete a database model, directly open the table "tblDatabase", go to the database model you want and delete the row. All entities, attributes and relationships will then be deleted also.
2.8 Adding further/changing existing colors
There are a few colors defined in the table "tblEntitiesColors". Some of them (except "Pink") will also be used as colors in ervin, but there as color names only, you cannot define the color. The columns "F_Color1" and "F_Color2" are used as gradient colors for yEd, you can change them like you want or add new ones. The simplest way to do that is to design the colors in yEd where you can find the hex color string in the properties window.
3 Further ideas
3.1 Tweaking the export
CCModeler is open to be changed by you to fit your needs. As you can see in the code module of frmMain it is not very hard to write an own exporter. So if you maybe want to create a graphviz export directly you can write your own exporter.
For that purpose two tables exists to make that easier: tblExportSettings and tblExportSettingTypes. The last one currently consists of one record only for graphml, add a row for each new export type (name doesn't matter).
The settings table consists of a foreign key for the export type to group the settings in a query in the exporter. The "F_ExportSettingValue" column contains the export text and some variables in the form "{variablename}" so in the exporter you only need a "Replace" function to replace variable names in curly brackets with the desired value. The "F_ExportSettingName" column contains a free to choose name for the export snippet. As yEd uses a simple XML structure it is easy to define a header and footer, then one for each entity (node in yEd) and one for each line between entities (called "edge" in yEd).
So if you have any other output format then add the desired settings here so you can simply import them using a recordset in VBA and only replace the variable names with the desired values as you can see with the yEd exporter. That keeps the code easy to read and if you need to change the output settings you don't need to change the code, simply change the setting value in this table.
3.2 Use it for auto documenting purposes
The code/tables of CCModeler can of course be used to auto create documentation grahics in own Access databases. Simply import any needed table/form/code module into your own database file and tweak the VBA code a little bit so you don't need to use the frmMain to import and export, just use the own database filename and a defined export name to create the output. In case of erviz you could also add a Shell command to the VBA code to directly create the diagram.
As you can also add comments to the relationships in CCModeler you can also add further export settings to add them as comments to yEd edges and so on.
Maybe this little tool helps to make diagrams faster than before, free for all purposes.
Have fun in experimenting with it
Christian
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (2)
Commented:
Author
Commented:thanks for publishing the article and the upvoting!
Christian