DB Design

Designing a Database

Over the last year and a bit I've been changing from a support based role to a development based role and I'm now at the stage where i'm learning to build my apps from scratch.

So far the hardest thing i have come across in relation to the whole process is database design... i have my brief of what i want to do and i start building.... to realised I've messed something up and dump all of my DB's again and rethink.

After speaking to the lead dev where i work he said:

Design your Database first, work out the relationships and core tables and you can update migrations later, it will make your life easier and also help with the build process.

Brilliant! grabbed a pen and paper and started designing my tables... then rubbing stuff out..... then scrapping and starting again..... then getting frustrated and leaving it for a day or two.

DBdiagram

I stumbled across this by accident and haven't looked back since! it has helped me with my DB structuring a lot as i can visualize instantly as i'm making changes...

So how does it work?

Its really simple like JavaScript nested arrays you specify tables like below;

    Project profileDB {
    database_type: 'MySQL'
    note: '''
    Notes about your database here
    '''
    }
    
    Table user {
      id int [pk, increment] // auto-increment
      username varchar
      first_name varchar
      last_name varchar
      email varchar
      }
      
    Table Profile {
     id int [pk]
     user_id varchar [ref: - user.id]
     rank varchar
     about varchar
    }

So what does the above do? Well it builds a linked diagram for you and it updates on the fly when you make changes to tables and names etc.

Take a look Here for a demo.

Want to share your work?

So the head Dev in my work is probably getting wound up with all the snippets and screenshots i send him with regards to DB design and up until the other day thats what i was doing... and then i came across another cool function with dbDiagram.... you can host it online and share a static link.... that updates when you push changes to it....

Is it Safe?

If you want to push it to a page so it can be access online you have two options

Default which is public and anyone can see it or secured with a password.... Password protection can be achieved when pushing you DB design to the branch and specifying the following command;

dbdocs password --set <password> --project <project name>

This will then stop anyone getting onto your DB Scheme design!.