In my last post, I wrote about ensuring that your data model properly handles global information : numbers, currencies, phone numbers, addresses, dates, and time zones, among other things. However, I’ve realized that many example data models have exactly the “self-centric” or “Amero-centric” approach that I cautioned against.As an American living abroad (for almost 30 years now), I often find that people make too many assumptions about the universality of what they know. For example, some Americans assume that others automatically understand their country’s ZIP code system, and its supplementary ZIP+4 version. In my experience, most of the world has no idea what a ZIP+4 is.
Very few database authors mention the challenges of globalization and localization in any meaningful way. There’s a similar lack of foresight from database architects. The fact is that many authors and designers are frequently very ‘self-centric’: they create (or write about) data models that only properly handle their local time zones, addresses, etc.A self-centric approach has a big problem: the resulting model will only support local data. In today’s Internet-fueled world, applications are often unexpectedly accessed by users around the globe. We need to support as much flexibility as possible for this international audience. Therefore, we need to design our data models with a globalized approach.
There’s a lot to keep in mind when you’re designing a database, and very few of us can remember every valuable tip and trick we’ve learned. So, let’s take a look at some online resources that feature database design tips and best practices. As we go, I’ll share my own opinions on the ideas presented, based on my experience in database design.Obviously, this article is not an exhaustive list, but I’ve tried to review and comment on a cross section of sources. Hopefully, you’ll find the information that best suits your needs and goals.
We all make mistakes, and we can all learn from other people’s mistakes. In this post, we’ll take a look at numerous online resources for avoiding poor database design that can lead to many problems and cost both time and money. And in an upcoming article, we’ll tell you where to find tips and best practices.Database Design Errors and Mistakes to AvoidThere are numerous online resources to help database designers avoid common errors and mistakes. Obviously, this article is not an exhaustive list of every article out there. Instead, we’ve reviewed and commented on a variety of different sources so that you can find the one that best suits you.
What’s In A Name? The Database EditionDatabase models require that objects be named. While several facets of naming an object deserve consideration, in this article we’ll focus on the most important one: defining a convention and sticking to it.Why Use Naming Conventions?Look at the database model below. I went a bit overboard and removed as many traces of a naming convention as I could. This proves my first point:a naming convention is an important part of a well-built data model
Over the years, working as a data modeler and database architect, I have noticed that there are a couple rules that should be followed during data modeling and development. Here I describe some tips in the hope that they might help you. I have listed the tips in the order that they occur during the project lifecycle rather than listing them by importance or by how common they are.1. Plan Ahead
In several of the projects we have worked on, customers have asked us to log more user actions in the database. They want to know all of the actions the users perform in the application, but capturing and recording all human interactions can be challenging. We had to log all modifications of data performed via the system. This article describes some of the pitfalls we encountered and the approaches that we used to overcome them.
In my second article about an online forum, I mentioned that there might be several more advanced features to be added:Forumcategoriesand sub-categories where each category has a subject, several moderators and additional information like creation date of the category.Apostmight have asubjectin addition to the content.We might want to allow users tovote upandvote downon threads and posts.To be able to grasp the model more easily, we have prepared an example of such a forum with categories, threads, posts, etc. We hope that this makes it easier to understand things:
Database design is the process of producing a detailedmodelof a database. The start of data modelling is to grasp the business area and functionality being developed.Before Modeling: Talk to the Business PeopleThis is a key principle in information technology. We must remember that we provide a service and mustdeliver value to the business. In data modeling that means solving a business problem from the data-side such that the required data is available in a responsive and secure way.
In my first article about an online forum, I mentioned that there might be several more advanced features to add:Moreformal details about the userinstead of a single “name” field. You may want the user’s first name, last name and username or nickname. A nice forum would also allow users to have a profile picture, email, roles, status (to allow users to be blocked), and other information like when they last visited the forum.