Database Design
I have been working on my database design this week for storing all the data for my application. I have based my design around my own current needs so it’s still draft 1. For a while now I have been using a spreadsheet to manage my macronutrition which is in the first image below. This spreadsheet works remarkably well and it has to be asked why I need anything else? The main reason is that it’s another chore to use, even though I have become really good at it, it does take time and sometimes I forget to fill it in.
Based on the above I drafted a first design of a database to record what I need it to and to support some of the functionality that I will want from my application. I have taken a photo of it here so you get the general idea.
The next stage was to create the database. I used the free Microsoft SQL Server Express Edition (Used to be called MSDE). I already had the grown up SQL Server 2005 Management Studio installed on my work tablet so I used that to create everything. I recreated my initial paper design into SQL choosing what I thought were the best data types (for now). I then created my relationships and foreign keys using the diagram tool which will feel familiar to Access DB people. The finished draft can be seen in the screen shot below.
I know that what I have here will probably be only around 50% of the design work, however it’s a start, and something to build on which is important. Trying to capture everything at this time and create the perfect schema would result in nothing being completed. This is based on what I already do using the spreadsheet but doesn’t cover everything I want it to do. Mockup’s will help me to decide on how to structure extra features.
Posted to WebApp
4 Comments »
RSS feed for comments on this post. TrackBack URI
Hi Tony,
I found your blog from Starr over at Step Lively Now I’ll be following your blog to see how you are doing. I’m in the same boat you are, trying to get my MicroISV off the ground. I need to go back and read the rest of your posts to come up to speed with how far along you are. I’m about to enter a full beta phase. I just started my personal blog about the technical and business development at My MicroISV Journey.
Good luck.
Comment by Chris — October 6, 2007 #
Talking about database design, why did you prefix all your table names with ‘tbl’?
From a database designer point of view if you use SQL Server 2005 Management Studio your tables will be listed under ‘tables’ so there is no need to specify in the name of each table that they are tables.
From a developer point of view, writing ’select GymId from Gym’ is very clear and (slightly) shorter than ’select GymId from tblGym’.
Also:
-why is your tblUsers table in plural but your tblGym table in singular? Are you implying that you will have multiple users in your User table but only one gym in your Gym table? Same with the Journal table etc. It’s your choice to have all the table names either in singular or plural but you should be consistent. I personally prefer all the names to be in singular as I find it simpler. If a user can be linked to multiple gyms, will you create a GymUser table? GymsUser? GymUsers? GymsUsers? If they are all in singular you don’t have to ask yourself such question.
-Why is your primary key in your Gym table called GymId but in your User table called UID (as opposed to UserId). Same with JID instead of JournalId,etc. You’re not consistent.
If you don’t pay attention to those details at this stage, I’m a bit worried about your final product. The database will be invisible to the end users so it doesn’t matter that much but if you’re as inconsistent with the UI that will frustrate a lot of people.
Comment by Anthony — October 21, 2007 #
You don’t seem to very consistent in your database design. I wouldn’t have published it at this stage as it looks like you lack some design skills.
Comment by Ian — October 22, 2007 #
Thanks for the comments guys. You do have some valid points but as I have said it’s a draft. Prefixing with tbl is something you love or hate. A lot of the database’s I look after on a day to day basis have 100’s of users with access front ends, some users really don’t know the difference between tables, views etc so prefixing has become a habit of mine that saves lots of problems when linking (both show as tables).
Gym isn’t plural because there wont be multiple gyms. A better name would have been tblGymActivities as the table is for recording activity you do at the gym.
Sometimes the obvious mistakes don’t appear until you start interfacing to the database and/or adding data. Hence why I said it’s only 50% of the final version and the UI mock ups will help me decide.
Just so you know that there are some tables omitting from this design as they would clearly give away some of the functionality I am not ready to share and to be honest I won’t be publishing my final design. This application isn’t open source it’s just a log of some of the things I have to do to create an app.
Seriously though thanks for the criticism, I believe there is no such thing as bad criticism, only constructive.
Comment by Tony — October 23, 2007 #