Designing a Data Model, process and tools to use…

0

Normalization is generally learnt during our college days from database concepts, but we tend to forget the very basic need for normalization as we acquire new knowledge over the years. We ignore normalization to avoid taking time to design our database. I mean, why have a bunch of tables, why not just 1 table with everything in it.

So why normalize our database ?

The answer is simple, databases need to be designed in a way so that the following issues are “prevented” at the root level itself by the design itself.

  1. if a record is deleted, other tables containing the record details should deleted in a cascaded format.
  2. Every table must have exactly 1 “intent”. Customer_Id, Payment_1, Payment_2, Payment_3 is NOT the way to design your database. Simply because retrieving data will be really slow. Now you are considering a few transactions, but in future you will not be given a dedicated server for just this one app for just this small set of users. In the real world, databases, filesystems, storage, network, cpu, memory is all shared. If your one app takes most of the cpu/memory resources, very soon it will be “out” of production.
  3. Performance, every database indexes tables to ensure fast retrieval. What does indexing mean, you ask ? Well, the database reads your table, and creates multiple pointers to your record, so that its not a sequential search. Creates a BTree, to prevent linear search.
  4. Foreign key fundamentals help keep your data “integrity” intact. (extension of point 1). Integrity here means, that your database has GOOD data, not stale not useless. Imagine a database that retains salary information of all employee’s even after they have resigned from the organization. Crazy, yes, its possible if you dont form the correct foreign key relationships.
  5. Oh forget all this, I’ll just take care of it in code. Well, if you take 2 mins to think about it, you will not need to write, test, maintain, bug fix 100-500 lines of code, if you just design your db/tables right.

I am not trying to convince you on the benefits of normalization, I am just reminding you. You are already convinced, but deep in your mind you believe that its needed, just lazy to put in that time. And I understand, database design needs time, thinking time. A step by step process, since it does take your whole brain to design it right.

The right tools play a very important role for designing your database. Remember it takes both the sides of your brain to design a database. So slow down.

I use ubuntu for my development work. For db design we need GUI tools. After some research I have zeroed on mysqlworkbench. It is a nice tool for reverse engineering your table and reviewing the schema graphically. Its quicker, less painful and a reverse engineered from existing tables. Not many tools produce this result.

To change relationships between tables mysqlworkbench sucks big time. Its a pain to use. So for this I use phpmyadmin. Its clumsy at best, but does the job.

So use phpmyadmin to edit relationships and review the change, I reverse engineer with mysqlworkbench.

phpmysqladmin, if you need to create a foreign key in table A, which is a column in table B. You need to create a index for the column in table B. Then Goto table A, “relation view”, and use the drop down to select your column from table B. Also set your “on delete”, “on update” rules.

I hope this is helpful. If not, please comment.

Interesting Links
For understanding workbench er diagram (http://www.smartdraw.com/resources/tutorials/cardinality-notations/)

MySQL Admin Tools (workbench vs open source browser based)

0

To start with, let me tell you that MySQL workbench sucks big time. I exported my tables to the workbench, and created my ER diagram. As I try to apply changes, all hell breaks loose. Obviously workbench has not sequenced its mysql statements in order correctly, so there are cyclic changes being made. Very immature of a product I must say. Does not incorporate graph theory fundamentals to solve sequencing of its mysql statements to apply the ER diagram to the DB schema. I mean, why create a bunch of mysql statements if you can run it. Over and above that we all know how pathetic mysql error messages are. Just crazy.

Solution
So I am only going to use WorkBench for viewing my schema, which makes it easy for me to think of my data model. Graphically seeing is a bonus. But use phpmysqladmin to do any changes to my schema.

Was trying to do things on workbench for last 3-4 hours, and finally 5 mins of phpmysqlamdin did the trick.