- KaiXinS -

Hello 2012! Let’s tidy up and start fresh

It’s that time of the year again - the beginning! The best time of the year (it is summer here) and even if I try not to, I make resolutions for the year ahead.

//TODO: Finish The BAKit Lite! 

Three weeks to go. Three weeks is a measly amount of time in the scheme of things. But with a trusty PLAN, everything will be fantastic (My only worry is for things left off the trusty plan).

I’ve done so much learning while doing this project (tossed in the deep end much?). The first thing I did when I got back to work this year was a complete overhaul of my database design. A lack of experience and a desire to build it like the familiar old database (for safety reasons) led to it being a spaghetti-like mess.

Here are some top tips I was given/picked up (yes, some of them are blindingly obvious, but I think I needed a list to stick to. These are based on my personal mistakes):

• Use clear and simple naming, following conventions. You don’t need to spell out relationships, anyone looking at your database will be able to figure that out on their own anyway. Association/Relationship tables should have clear names following the convention [table1][table2].
• Always name your foreign keys correctly! e.g. [‘table name’Id].
• Avoid duplicating information in separate tables, especially if the data might change frequently. 
• Make sure you don’t use depreciated types (especially if you are borrowing code from an old system).
• Consider check constraints in the database and enums in the code for keeping categories.
• Only add relationships if tables are clearly and naturally related. My one exception for this was a security constraint - that all tables had a Tenant Id as a foreign key (An Id to identify which system the data belongs to, as ours is a multi-tenant database and security of data is very important).

There you have it! Of course I wrote all of these down in my trusty notebook for next time, should I endeavour to recreate such nonsense as my previous database. Or something.