WWW SQL Designer Tutorial for Database Schemas

Creating ERDs

ERD (Entity Relationship Diagram)

• Copy of WWW SQL Designer Hosted for Class, USE THIS, less buggy, has unsigned INTs
• WWW SQL Designer

Initial Configuration of WWW SQL Designer

• Do this BEFORE you create your ERD as you must reload the web page for these changes to take effect!
• Go to the Options:
  
• Choose both of the bottom options that show data type and size
  
• Reload the web site for these options to take effect (WARNING! If you have already started some design work, use the SAVE/LOAD option to save the xml first or you will LOSE all your work!)
• After you have made these changes, your tables should look like this (note, the later images do not show the options as I forgot to turn it on when I built that part of the help page)
  

Adding Tables

1. [Add Table] (click and place table on the grid)
2. Type the name of the table (ie "books" )
3. Click on the "id" field in your new table
   (it is bold as it is the primary key)
   • Choose "Edit Field" from the menu
   • update the name as appropriate (ie "book_id" )
   • Choose INT UNSIGNED as the data type (Note: on my version of www sql designer only)
   • remove "NULL" from the "Default:" field
   • uncheck the "NULL:" field
     
4. Then "Add field" as needed to add as many fields as needed to your tables
5. Remember to choose the appropriate data type for the field
6. DO NOT SET "NOT NULL" for anything other than the Primary Key unless you are SURE that it will never ever ever need to be blank
7. The "size" field should only be used for
   • CHAR and VARCHAR (just put the number, say 200, do not put quotes or parentheses)
   • DECIMAL (just put 8,2 no quotes, no parentheses, this will give you 8 digits, two of which are reserved for the decimal place)
     
8. Do not create a field for the Foreign Key as of yet (we will be making a publisher_id FK in the books table, but do NOT use "Add Field" for that)

To create the FOREIGN KEYS

1. select the PK from the table that has it
   • ie: click on "publisher_id" in the "publishers" table
2. click on "Create Foreign Key"
   
3. click on the table where you want to place the FK
   • ie: books table
4. highlight new FK and edit it to remove that dumb _tablename that gets added
   • ie: publisher_id_publishers to publisher_id
     
     
     
5. Depending on the table, you may WANT to check the NULL box for the FK. (you might not know the publisher for a book, so allowing the FK only to be NULL makes sense) for a join table, like books_authors, leaving the book_id and author_id as NOT NULL is probably best.

Save your work (This is the design, CRUCIAL that you save this)

1. click on "SAVE/LOAD"
2. "SAVE XML"
3. use [ctrl][a] to select all in the input/output window
4. then [ctrl][c] to copy
5. paste into notepad++ or appropriate text editor (NOT notepad)
6. save it as something.xml

Restore or open your work

1. open your something.xml file
2. ctrl-a to select all
3. ctrl-c to copy
4. in www sql designer click on "save/load"
5. paste into the window that is on the wwwsqldesigner save/load screen
6. click on [load xml]

Generate MySQL - NOTE: You HAVE to do some editing

1. 'Save/Load'
2. 'Generate SQL(mysql)'
3. copy and paste results to an editor
4. NOTE 1. If you delete a PK (say in a join table) you will see an empty PRIMARY KEY() still there
5. Either delete that PRIMARY KEY() or if needed, convert to UNIQUE KEY (fieldname(s))
6. MAKE SURE TO REMOVE THE COMMENT marks (-- ) in the Table Properties section that set the engine=InnoDB and set the charset and collate BEFORE you do the alters to set up the FK
   • You basically want ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; to be the set up
   • Either fix the ALTERs or put this after the ) in the CREATE TABLE sections.
7. For the PRIMARY KEYS and the future (once you do the ALTERS) Foreign Keys
   • remove the NULL and DEFAULT NULL's (if you forgot to do so when you made them)
   • NO LONGER NEEDED IF YOU USE MY VERSION and choose INT UNSIGNED, OTHERWISE add UNSIGNED (so INT USIGNED) for both the PKs and the FKs
8. For safety sake, get rid of the DROP TABLE name if EXISTS;
9. Paste the cleaned up code into your mysql client and see if it works :)
10. IF you change the design, you have two choices
    • Use the GENERATE SQL(mysql) and re do all your fixes OR:
    • change your design, then manually edit your .sql file to match your changes