Answers to database design questions by Grace Elaiza Seballos, Davao City, Philippines
Date: 4 January 2015
Please explain (or briefly describe) the general philosophy behind your database design approach.
Normalization. This doesn't need philosophy or anything. It is very basic. Data integrity and speed is also very basic. With my 10 years of experience, I have already created my own systematic way of developing systems. The most important part of any SDLC is the way of being able to address the needs and pains of my clients, and even finding potential pains they may experience without them even realizing. After streamlining all of that, software development is just then putting it into a conveyor belt in an automated factory, while waiting for the finish product to be tested. The important thing here is, what setting were given into that factory? And that setting is what I will need from you when we get into the details of the system.
What is something you have seen done repeatedly in database design work done by others people which you find to be particularly annoying or detrimental?
I do not know. The reason is, I do not look at anyone else's work. I mind my own business. The only competitor I have is myself. I have developed my own architecture since 2005, and have been improving it year by year. If your are going to ask my about MS Access, I have experience with MS Access. And I have stopped using MS Access for a production environment. What I have been doing was upgrading people from using MS Access. It is not an RDBMS. It is not meant for heavy transaction usage. If it was, Microsoft will not invent MS SQL Server. Some experts will try to find a work around. I say, that is a waste of time. Why reinvent the wheel?
Given that you are database specialist, how does what do you while creating your database architecture save clients time and/or money?
First and for most, gather and analyze what my clients needs. I already have my own standard architecture. I no longer need to think about that. It has been tested over and over again since 2005, in different types of systems for different types of industries. I created this architecture so I can focus on these 8 tenets (in which I believe that you should also be looking for. Expert level skills are easy to find. But an individual whose skills were tested and tempered are more worth having) .
1. Systems should be able to improve communication, so you can make better decisions.
2. Systems should be able to increase productivity, so cost and time can be saved.
3. Systems should be able to improve customer service. This will give you happy customers.
4. Systems should be able to reduce stock outs, so you can increase sales. (should your system include inventory)
5. Systems should have an efficient collection process, in return, you get an increase in cash inflows.
6. Systems should be able to optimize inventory levels, and therefore, decrease your tied-up capital.
7. Systems should be able to monitor costs and expenses, in order to maximize the profits.
8. Systems should be able to give you less time in managing your business, so that you can have more time growing it and yourself.
Where do you live?
I live in the Philippines.
Briefly describe your pertinent work and/or educational background.
I am currently a Freelancer system analyst/web developer. I have been in this industry since 2003. I was in college back then while I was also an OJT in a software development firm. I was accepted at the firm by 2005. It was in 2010 that I started to become a freelancer.
Your rate is higher what other programmers and freelancers charge. How does what you do with database design save clients and companies money in the long run?
I do believe I have a little bit above average hourly rate. But it is actually cost effective. This is because, I also finish faster that the average programmer without sacrificing quality. It is not only due to database design that my clients are able to save in the long run. I believe it is because of my 3-tier approach architecture. Even if the client wants to change requirements in the middle of development, my architecture can still adapt to it. I was trained in the environment of software development that focus on custom-made and tailor-fit systems, and not out-of-the-box systems. I believe that it is the system that should adapt to the needs and pains of the client, and not the other way around.
You have special expertise in database design. How is what you do different from what a generalist computer programmer might do when it comes to creating a database back end?
I do not really what makes me different from generalist computer programmer. Maybe if I will compare myself back in highschool, the time where I still do not know how to use SQL. I create my own database management using "Bucket Chaining" technique. When I was an OJT of a software development firm, that is where I learned about SQL, normalization, and data integrity.
How is the database design work you do distinct from and also integrated with other aspects of overall software/technology systems?
I treat database as only a database. And a database should only do 4 things, and that is "Insert, Update, Delete, and Read". You will not find me using triggers or stored procedures. I always treat the database as a separate layer, and should not be combined with the other 2 layers (business logic and presentation/UI layer).
Describe how the database design work you do now is similar to or different from "textbook database design," such as one might learn in college or read in a text book or software manual.
It is more improved due to experience. In college days, or in texbooks, most of them are just samples. When it comes to real life, it becomes very different especially when you become exposed to different types of business logic in different industries. You get to know stuff (not really technical) through experience and mistakes. Like for example, in the old days, I would put the "price" field in a product table. But in my experience, that doesn't really work. The reason is, product prices changes overtime. If you change the price in the product table, this will affect every past reports that should have the computation of the old product price in it.
What is the biggest example of a "rule" you routinely break? (Or another way of putting it is: what popular tradition are you most likely to depart from, if any?)
Sometimes, I have to weigh things between getting a table normalized, or fitting it in such a way, that once I create a query for it, it will be easy. One example would be an "inventory" table. The inventory table have a child table where the inventory movement history takes place (whether adding or removing inventory for that bin). I should not put the quantity left on the parent table, because it can already be computed from the child table. But for simple query purposes (especially when making statistical reports), it can be more beneficial to add the quantity field on the parent. But this is still a case to case basis, for the reason that all RDBMS support Views already. Therefore, I can create Views (ready for the reports) instead of querying directly to the table. It is much faster and more reliable.
What RDBMS have you used extensively, or are at least somewhat familiar with based on first-hand experience?
Back in college, most of what was used in school was MS Access. In my OJT, we were using Firbird SQL for 2 years. And then we shifted to MS SQL. And we also had clients using Oracle. On 2008, I was exposed with MySQL (innodb engine). Since 2010, most of my clients are either using MS SQL or MySQL. But it wouldn't really matter in my opinion. I only use functions that are available on these 3 RDBMS (MS SQL, MySQL and Oracle).
Given an assignment to create a website from scratch for Company X, what RDBMS are you using? (website specs: principle table has 10,000 rows and will grow to 200,000 over next five years; admin back-end, general browsing, user logins; no more than 100 concurrent users)
That depends on the company. If this system is their "bread and butter" to either save on their expenses, or increase on their sales (a must in their day to day operations), and the company is a large enterprise corporation, I would first suggest to them to use Oracle. Nothing beats Oracles (except their price). Mostly likely, they won't want Oracle pricing (unless they already have peoplesoft). So, by average, in my experience, most will opt for MS SQL Server. I usually only would suggest MySQL last and usually only because if the website they need is nothing more than basic features.
What other technologies are you using for this website?
What tools would you be using? (IDEs, code editors, database design tools, if any)
For IDE, I currently use Visual Studio 2010. Visual Studio 2013 is up and free but I am still waiting for feedback on that. I used Mozzila Firefox as development browser. I use MS SQL management studio for database management on MS SQL. I used TOAD if database is Oracle. I used TOAD and SQLyog for MySQL. And I used my own architecture to do those redundant programming stuff. I use Adobe Photoshop for image editing (I just have average skills on this). I used GIT Extensions for source code control. And I use dropbox as backup for my source.
Aside from the database design, how much other development do you typically do? How much would you prefer to do?
It is an all-in-one package. I cannot only do database design and leave it at that. I want to create a complete system. Even during the "drawing board" phase, I already have the finished product in my mind. I have to see through it to the end. From Inception, to development to transition.
Table names: plural? singular? or something else? why?
I do not use plural (except when it doesn't seem to sound right, like sales_order sounds better than sale_order). Table names are always in small letters. The names should be the same as the module name, but should be divided by an underscore "_". For example: The module Request for Leave. Then the table name will be "request_for_leave". Why? I just chose that every since I created my architecture in 2005. It is much easier for the architecture to integrate the Data Layer to the Business Logic layer when there is some form of standard. During those days when I was developing with a team, it would also make it easier for the team to debug the system. If the module name is Request for Leave, then think no further, the table for it is "request_for_leave".
How do you name your primary identity fields?
I have 2 kinds of tables. I call them transaction tables and library tables. And each of them have standard fields that will always be there. But I will not delve further about that in details. In both of those tables, there is always an "ID" field and a "Code" field. The "ID" is the primary key. The "Code" is not the primary but is a unique field. The ID is always system generated. And that is used to connect with a foreign key. The "Code" field is not related to anywhere but will just be a unique field. It is exposed to users for editing (if the client wishes) or also a system generated field. As opposed to the "ID" field, the ID is invisible to the users. This way, the users can keep changing the "Code" of a product, but will not affect the foreign keys anywhere because the "ID" is the primary key.
Describe your relationship (if any) with WordPress?
I do not know how to use Wordpress.
If you use stored procedures, how do you decide to use them or not use them? What are your alternatives?
I do not use them. The reason for this is that, I use the 3-tier architecture approach. The data layer, the business logic layer, and the presentation layer are always separated from each other. As I have mentioned before, my skills are honed with custom-based and tailor-fit systems. This means, if I have completed an Inventory System for a client that is using a Oracle database, and then, sometime down the road, the user want to change the database int My SQL. Then I do not have to program the business logic and presentation layer anymore. All I have to do is to configure my data layer for My SQL. And then my client wants a mobile version of the inventory system, then he/she just needs to find a mobile developer. No need to reprogram the business logic or explain to the mobile programmer about the database structure. The Data Layer always communicate with Business Logic Layer. And the business logic layer is responsible to send data from the presentation layer (mobile) and receive data from the presentation layer. This data is then processed by the Business Logic before it is sent to the Data Layer. Then the data layer will just save it into the database (wether commit or uncommit depending on errors encountered during RDBMS interaction). This actually is the solution in order to have a cost-effective system. The mobile programmer will just create the input UI, and send those input to the business logic. Business Logic will do all calculations. Mobile programmer doesn't even need to know how the inventory is to be saved or computed.
Why would a client who needs database design work done be making a wise move to hire you (if you're available)?
I am not only for database design work. I am a whole system package. I want to know how everything works, and I have to be there from start to finish. That is the only way I can also give the optimum solution to the needs of my clients. The data layer, the business logic layer, and the presentation layer need to have an architect who knows who these three work hand-in-hand seamlessly. That is me.