{"id":46,"date":"2006-05-02T17:31:33","date_gmt":"2006-05-02T22:31:33","guid":{"rendered":"\/cgi\/wp\/?p=46"},"modified":"2006-05-02T17:58:18","modified_gmt":"2006-05-02T22:58:18","slug":"how-to-document-a-mysql-database-design","status":"publish","type":"post","link":"http:\/\/williamsportwebdeveloper.com\/cgi\/wp\/?p=46","title":{"rendered":"How To Document A MySQL Database Design"},"content":{"rendered":"

My blog software is WordPress which is using a MySQL database. My task is to document the database design. Let us see how I accomplish this goal. <\/p>\n

My first step is to back up the database because I need a local copy to work with. I log into phpMyAdmin 2.5.0<\/b> and export the database as a SQL text file. There is no need to describe this step because it is well documented on the Internet. This gives me a phpMyAdmin MySQL-Dump file which reveals the database design but not in a format I find very readable.<\/p>\n

Next I want to import the database backup into my local MySQL Database Server. First I need to create the database “wordpress”, then I use the MySQL Administrator’s Restore option to restore the database using the SQL file, making sure the target schema is my new database and the checkbox “Create database(s) if they don’t exist” is checked.<\/p>\n

Then I download and install the ODBC driver for MySQL. You can get it here: http:\/\/dev.mysql.com\/downloads\/connector\/odbc\/3.51.html<\/a> I want to create a SQL Server database version of the WordPress MySQL database so I create a new SQL Server database and start the DTS Import \/ Export Wizard. <\/p>\n

Because I installed the ODBC driver for MySQL I now find a new option in the data source drop down list:<\/p>\n

\"MySQL-DTS-Import-Export\"<\/p>\n

When I select that option I discover that I need to create a Data Source Name for the MySQL database I want to import:<\/p>\n

\"MySQL<\/p>\n

I click the Test button and find I can connect to the database:<\/p>\n

\"Success;<\/p>\n

When I attempt to import all the tables, I get 11 tables copied but 2 show errors:<\/p>\n

\"DTS<\/p>\n

There was an error importing the wp_links table Error at Destination for Row number 1. Errors encountered so far in this task: 1. Insert error, column 11 (‘link_updated’, DBTYPE_DBTIMESTAMP), status 10: Integrity violation; attempt to insert NULL data or data which violates constraints. Unspecified error<\/i><\/p>\n

\"wp_links<\/p>\n

And an error importing the wp_users table Error at Destination for Row number 1. Errors encountered so far in this task: 1. Insert error, column 13 (‘dateYMDhour’, DBTYPE_DBTIMESTAMP), status 10: Integrity violation; attempt to insert NULL data or data which violates constraints. Unspecified error<\/i><\/p>\n

\"wp_users<\/p>\n

To solve these errors I just edit the SQL Server database design for these tables and allow these fields to accept NULL values:<\/p>\n

\"wp_links<\/p>\n

\"wp_users<\/p>\n

Then I attempt to import just these two tables again and this time it works:<\/p>\n

\"Executing<\/p>\n

Since that went pretty well I attempt the same thing with Microsoft Access. I try to import the MySQL database using ODBC Databases as the file type and select my data source name which already exists now. Unfortunately Microsoft Office Access has a problem and needs to close:<\/p>\n

\"Microsoft<\/p>\n

Since that does not work I just export my SQL Server database to my Access database which succeeds with no errors:<\/p>\n

\"Sucessfully<\/p>\n

Now that I have all my tables and data in Access I can document the database design using its documentation wizard. Select Tools<\/i> from the menu and then select Analyze<\/i> and then Documenter<\/i>. Click the Select All<\/b> button and then the OK<\/b> button.<\/p>\n

\"Access<\/p>\n

Now I can export the table design reports as web pages so you can view them online. Select File<\/i> from the menu and then Export<\/i>. Select HTML as the “save as type”. The table design reports will be exported as web pages complete with navigation links. You can view the talbe design of the MySQL database used by the WordPress blog here:<\/p>\n

http:\/\/www.williamsportwebdeveloper.com\/design\/wordpress_db.html<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

My blog software is WordPress which is using a MySQL database. My task is to document the database design. Let us see how I accomplish this goal. My first step is to back up the database because I need a … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"_links":{"self":[{"href":"http:\/\/williamsportwebdeveloper.com\/cgi\/wp\/index.php?rest_route=\/wp\/v2\/posts\/46"}],"collection":[{"href":"http:\/\/williamsportwebdeveloper.com\/cgi\/wp\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/williamsportwebdeveloper.com\/cgi\/wp\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/williamsportwebdeveloper.com\/cgi\/wp\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/williamsportwebdeveloper.com\/cgi\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=46"}],"version-history":[{"count":0,"href":"http:\/\/williamsportwebdeveloper.com\/cgi\/wp\/index.php?rest_route=\/wp\/v2\/posts\/46\/revisions"}],"wp:attachment":[{"href":"http:\/\/williamsportwebdeveloper.com\/cgi\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=46"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/williamsportwebdeveloper.com\/cgi\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=46"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/williamsportwebdeveloper.com\/cgi\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=46"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}