If you ran a MOOC online learning platform like edX or Coursera, how would you keep it organized? In this article, we’ll look at a database model that would do the job.You’ve probably heard of MOOC (Massive Open Online Course), a trending way to learn online. And if you haven’t, think of a MOOC program as university subjects with all materials, tests, and feedback available online. Two of the most popular online MOOC providers are
Call them taxis or cabs, these convenient rides for rent have been around for centuries. Nowadays, it’s a lot more complicated to run a taxi service. In this article, we’ll look at a database model designed to meet the needs of a cab company.The history of “calling a cab” began in 17thcentury London. In most places, cabs are more affordable than ever. They are also becoming a lot more accessible: we can order a taxi by phone, via mobile applications, or on the Web. Or we can use the same techniques that have been working for hundreds of years – line up at a cab station or flag one down on the street.
In general, people do not like receiving unsolicited e-mails. Nevertheless, they sometimes subscribe to newsletters in order to get a discount or to keep up-to-date with new products. This article will present one approach to designing a newsletter database.Why Worry About Newsletter Emails?Newsletter subscribers represent an extremely valuable group of clients – they are interested in our products, they trust us, and they spend time reviewing our offers and promotions. What is more, sending emails to clients is one of the cheapest tools in online marketing. However, it needs to be done carefully – data must be updated daily (because people subscribe and unsubscribe) and be high-quality (we do not want to send unwanted emails, as it negatively impacts brand image).
Everyone planning to move to a new location looks for good accommodations. In the Internet era, the very first place they tend to look at sites dedicated to listing rental properties. According to a poll by one well-known agency, 76% of people looking to rent turn to the internet first; this figure rises to 88% for those aged 25–34.Let’s dive into a full-fledged data model that supports such sites and their underlying features. Our model also enables site users to conveniently filter search results.
What part does database design play in running a restaurant? What might the data model for a restaurant database look like? Find out in this article.A restaurant serves people with ready-made food. This is a type of business that is thriving all over the world, and often with a lot of flare. People feel very comfortable going to restaurants, and they are starting to expect a wide range of options when it comes to their next meal.
Everyone likes to play games, especially the simple ones. Tic tac toe is about as simple as you can get, and despite its age it is still widely played. In the past, the only way to play tic tac toe was on paper; now there are plenty of computerized options. In this article (inspired by our recent post, A Database Model for Simple Board Games ), we’ll present a tutorial on building a tic tac toe web application with Spring Boot and AngularJS. The final code is hosted on GitHub.
On hearing what I do, people tend to ask me the same question:Can you develop a system that predicts football match results? Or Olympic medal outcomes?Personally, I don’t put much faith in predictions. Still, if we had a large amount of historical data and relevant indicators, we could certainly design a system to help us come up with more accurate assumptions. In this article, we’ll consider a model that can store the results of matches and tournaments.
Relationships are everywhere: between people, between organizations, between organizations and people. Think about being an employee of a company, being a member of a project team, or being a subsidiary of another company. Is there a straightforward way to accurately model and manage all these relationships? Can we easily answer the question ‘Who knows who?’A Quick Review of RelationshipsExactly how this basic model was derived was described in my previous article,
The bill of materials design pattern is deceptively simple, yet incredibly powerful. This article will introduce an example, familiar to IT professionals, that you may not have thought fits the BOM pattern. It will also introduce concepts to show you how to make your BOM structures more flexible and much easier to manage.A Short Recap of the BOMAbill of materialshas its roots in manufacturing. It is a list of the raw materials, sub-assemblies, intermediate assemblies, sub-components, parts, and the quantities of each needed to manufacture an end product.
The bill of materials (BOM) design pattern is deceptively simple, yet incredibly powerful. Historically, it’s been employed to model product structures, but the pattern can be used to do much more than simply define a hierarchy. This article will introduce three very different examples to help you to recognize the pattern in your own projects.What Is a Bill of Materials, or BOM?Abill of materialshas its roots in manufacturing. It is a list of the raw materials, sub-assemblies, intermediate assemblies, sub-components, parts, and the quantities of each needed to manufacture an end product. You can look at it as a hierarchical decomposition of a product. Other terms for the same thing are product structure, bill of material, and associated list.
How to design a database flexible enough to accommodate several very different card games.Recently, we showed how a database could be used to store board game results . Board games are fun, but they’re not the only online version of classic games going. Card games are also very popular. They introduce an element of luck into gameplay, and there is much more than luck involved in a good card game!In this article, we’ll focus on building a data model to store game matches, results, players and scores. The main challenge here is storing data related to many different card games. We could also consider analyzing this data to determine winning strategies, improve our own playing skills, or build a better AI opponent.
In this era of globalization, companies – including software developers – are always interested in expanding to new markets. This often means localizing their products for different areas. In this article, we’ll explain a few approaches to designing your data model for localization – specifically, for managing content in multiple languages.What Is Localization?Localization is the process of adapting a product to various markets. It is a prominent factor in achieving maximum market share in terms of product sales. When localization is done correctly, users will feel that the product was produced for their language, culture, and needs.
This is the fourth in our multi–part series on data modeling for information security as well as data characteristics. A simple data model for a fictional website that supports shared–interest organizations (bird–watching clubs, etc.) has provided us with content for exploring data modeling from a security viewpoint.In Oscar Wilde’s playLady Windermere’s Fan, Lord Darlington tags a cynic as “somebody who knows the price of everything, and the value of nothing.” Sadly, the information in our databases can be unconsciously treated in the same way. Is a customer account worth the sum of its purchases? What do we suffer if we lose four hours of marketing data during holiday shopping season?
The first part of this series introduced some basic steps for managing the lifecycle of any entity in a database. Our second and final part will show you how to define the actual workflow using additional configuration tables. This is where the user is presented with allowable options each step of the way. We’ll also demonstrate a technique for working around the strict reuse of ‘assemblies’ and ‘sub-assemblies’ in a Bill of Materials structure.
Have you ever come across a situation where you need to manage the state of an entity that changes over time? There are many examples out there. Let’s start with an easy one: merging customer records.Suppose we are merging lists of customers from two different sources. We could have any of the following states arise:Duplicates Identified– the system has found two potentially duplicate entities;Confirmed Duplicates– a user validates the two entities are indeed duplicates; or
People like to play. So we invented games and placed them on boards. And then computers came. Now we can play against the computer or against human opponents all over the world – or right next to us.When we play against human opponents we want to have a ranking that will show others how good we are. Maybe it will scare opponents. Or we could just rub someone’s nose in our greatness. Or use our stats to feed our ego. And it would be nice if we could review previous games and learn from our mistakes.
This is the third of our multi-part series on applying information security approaches to data modeling. The series uses a simple data model, something to manage social clubs and interest groups, to provide the content we look to secure. Later we will address modeling for authorization and user management, as well as other parts of a secure database implementation.In social situations, it’s common to “read between the lines” – deducing the unspoken assumptions and assertions in a conversation. The same occurs in creating software and storing data in a database. Invoices are enumerated with the customer ID embedded, and how many data entities use a date-time as part of the key? It’s hard to imagine thoroughly documenting or structuring everything without some type of omission. But in our last instalment, we went through exactly that exercise. We were able to ascribe sensitivity to several parts of our social club database. But to quantify and manage that sensitivity, we must augment the structure of our data model in order to make the sensitive data and its relationships clear.
Celebration!! Family time!! Long drive!! A day at the beach!! All these words buzz into our minds when we think of holidays. Have you ever considered how a multinational company keeps track of holidays across the globe? There must be a data dictionary to maintain all these details so that they can ensure seamless business with their local partners.This article will explain such a data model.The Project Requirements in a Nutshell
Early in the movie “The Fellowship of the Ring”, the wizard Gandalf asks the hero Frodo this question:“Is it secret? Is it safe?”We may not have a magic ring to protect, but we’re asking the same question. But we’re talking about information.This is the second in a multi-part series on how to apply information security principles and techniques as part of data modeling. This series uses a simple data model designed to manage non-commercial clubs as an example of security approaches. In later articles, we will address modeling for fine-grained access controls, auditing, authentication, and other key aspects of secure database implementation.
In Part 1 of this series, I demonstrated how to install WordPress locally and how to import a WordPress database into Vertabelo . In this article, we’ll take a closer look at the tables in the WordPress database.A Quick Look at the WordPress Database Model and the DashboardIn the previous part , I imported the WordPress database into our online database modeling tool. For the record, the structure of the database is as follows:
How often have you wondered about the structure of an existing database? It could be a 5-year-old legacy system or a brand-new open-source project. Let’s take a look at the ERD diagram of the database behind the most famous content management system: WordPress.What Is WordPress and How Does It Work?WordPress is an open-source CMS (content management system) that was initially released in 2003. It started out as a blogging platform, but it has developed so much that today it can be used for almost anything.
Nowadays, carpooling is accepted and promoted by people around the globe. It certainly reduces one’s personal carbon footprint, and it can be more cost-effective than renting or buying a car.Carpooling also takes a lot of work – organizational work that can readily be done by a well-designed database. This article explains a detailed data model that a carpooling website could use.Data Design, Meet CarpoolingSo, we need to design a data model for a ride-share (aka carpooling) website.
You might have rented a car on your last vacation. You reserved your car online, and then picked it up from its designated location after paying all the previously-agreed charges. Once you were done, you returned it to the agency and perhaps paid some additional fees. Did you ever think about the system that makes all these things happen? In this article, we’ll look at a data model for a car rental system.
I wrote a song about dental floss but did anyone’s teeth get cleaner?Frank ZappaWhen we think of the dental office, our first associations are the drill, the pain, and the fear. OK, that sounds bad. Besides taking care of teeth, a dentist has many other obligations that are professional, legal, or both. All of them require proper data management.To meet this documentation requirement, many dental and medical offices use paper records. Slowly but surely, though, there’s a trend towards the digital records and management of the 21st century.
An investment in knowledge pays the best interest.Benjamin FranklinIn the modern world, education is omnipresent. Now more than ever before, it plays an important role in our society. It’s so important, in fact, that many of us continue our education well after finishing school or college.We have all heard of lifelong learning, non-formal education, and workshops for all ages. These methods differ from formal education in many ways, but they also have things in common. There are classes, lessons, teachers, and students. And just as in a traditional setting, we’ll want to keep track of the class schedule, attendance data, and instructor or student achievement. How can we design a database to meet these needs? That’s what we’ll cover in this article.
Buying books was a way anyone could acquire a work of art for very little.Solomon “Sol” LeWitt,American artist,1928–2007Selling products and services can be two very different propositions. This originates in their differing definitions and real-world representations. Previously in this series, we discussed the table basics in the context of database design and sales. In this post, we’ll analyze the differences between products and services, how they impact the database model, and how we can accommodate both on one database.
People love to communicate. We often joke that any software system always evolves into a messaging system. This article will explain the system requirements and step by step approach to design a data model for a messaging system.Requirements in NutshellThe core functionality of a messaging system in an application is tosend notifications/messagesto a user or a set of users. Our system also allows one to send messages to a user group. User groups can obviously be formed on some parameters like access privileges, geographical location of users, etc.
“Information is the lifeblood of any organization…” We hear a lot of statements like this, or about an “information age,” or an “information economy.” When we agree with belief that amplifies the importance of information in the world today, we have to consider how to make that all-important information secure. Who can see my bank account? Was the facilities maintenance contract lost? Why can’t I get the latest lab report? The database professional has such concerns magnified by the thousands or millions, and so modeling for data security is critical. Here we look at some issues in securing and controlling access to information stored in a database.
Storing sales data properly and later combining it can lead to creating a predictive model with a high rate of accuracy. In this and the next few articles we’ll analyze a database design for recording sales.Everyone lives by selling something.Robert Louis StevensonIn today’s world,selling productsis ubiquitous. And salespeople who have access to robust tools that leverage historical data to analyze trends and enable an enterprise to adjust business strategies accordingly have an advantage over their competitors. There are lots of parameters that can affect company results: the current global economic situation, clients’ location, age, material and marital status, and history of previous contacts or sales to clients.
Death and taxes – add “software problems” to that list of the inevitable. There is always a new issue, a new failure, a new key opportunity that an organization must address. And to avoid repeating the problems, or to revise your prior fixes, it is critical to capture the problems accurately and completely. You need a history of what happened and when. In this piece, we create the logical model for a problem or “bug” reporting system.
There are many ways to solve a problem, and that’s the case with administering roles and user statuses in software systems. In this article you’ll find a simple evolution of that idea as well some useful tips and code samples.Basic IdeaIn most systems, there is usually a need to haverolesand userstatuses.Roles are related torightsthat users have while using a system after successfully logging in. Examples of roles are “call center employee”, “call center manager”, “back office employee”, “back office manager”, or “manager”. Generally that means that a user will have access to some functionality if he or she has the appropriate role. It’s wise to assume that a user can have multiple roles at the same time.
Let’s build further changes into the data model, which I created in my earlier blog post , such as having an automated approach to assigning an instructor and vehicle to a lesson, invoicing to customers and tracking of them.First off, I need to build logic on the application side to assign an instructor and vehicle to lessons before they actually take place. The main thing to ensure here is availability, i.e. an instructor or vehicle can be assigned to a lesson only if both of them are available on the scheduled time of the lesson.
It’s common knowledge that the best way to learn something is to practice it in a real-life scenario. Obviously, the same applies to database modeling. Therefore, in this article I decided to teach you how to create a simple database structure, taking a textbook example of a hotel room reservation system. I will show you how to get started and give you some ideas for extending the model.Database Modeling: Discover, Discover, Discover
I need to design a data model for a reservation system for a driving school. The subject area looks quite straightforward, but complexities are still involved. You have to track all requests from clients and keep track of resources (vehicle, time and instructor) consumed during lessons.IntroductionI like to use adomain driven approachfor designing a data model. It makes me put technology obsession aside and concentrate primarily on modeling the subject area revolving around its associated entities and relationships amongst themselves.
Do you like going to the movies? Have you ever considered what the database design behind their reservation system looks like? In this article we’ll prepare an example database model for a movie theater.There are a few assumptions we have to bear in mind:contemporary multiplex movie theaters can have one or more auditoriums within a larger complex,each auditorium can have a different number of seats,seats are numerated with row number and seat position within a row,
Modern applications have plenty of authentication features beside registration and login. In this article we will take a look at how to design the database for two such features: email confirmation and password recovery.Email ConfirmationWhat Is It?Most people familiar with the Internet know what an activation email is. An activation email is sent to the user after he or she registers for an account on a website or web application and contains a link that will allow the user into the system. The email is primarily used to ensure that the email address provided by the user during registration is
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:
How difficult is it to program a user login function for an application? Novice developers think it’s very easy. Experienced developers know better: it is the mostsensitiveprocess in your application. Errors in login screens can lead to serious security issues. In this article we take a look at how to store authentication data in your database.The most common way to authenticate users nowadays is withuser nameandpassword. How do you store this information in a database? Obviously, you should store login info in one table. The name of the table is up to you. My advice is to
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.
In this final article in a four-part series, I complete the design for an online survey database to provide flexibility for multiple surveys, question re-use, multiple choice answers, ordering of questions, conditional jumps in the survey based on responses, and control over the users’ access to surveys via groups of survey owners.IntroductionIn the conclusion to Part 3 of this series of articles, I mentioned that I would be adding more advanced features in this article. Those advanced features are:
In the conclusion to Part 2 of this series of articles, I mentioned that I would be adding more advanced features, such as:Conditional ordering of questionsin a survey or, in other words, the possibility for a conditional path through the surveyAdministrationof the surveyReportsandanalyticsIn this third article related to anonline survey, I will extend the functionality to support conditional ordering of questions.In the future, we may add questions that require a rated response. For example: “How much do you like database design, rate between 1 and 100 (with 1 indicating that you like it very little and 100 indicating that you like it immensely)?”
In part 1 of this article series, we discussed a basic design for an online survey. In the conclusion to that article, I mentioned part 2 would cover more advanced features for our survey such as:Different types of questions such asmultiple choice questionsConditional order of questionsin a survey or, in other words, the possibility for a conditional path through the surveyAdministrationof the surveysReportsandanalyticsLet’s start by extending the functionality to support different types of questions.
I need to create the design for a new database which will be thedata layerfor an application; the application will be an onlinesurveyor polling like Survey Monkey. My challenge is that the functionality that I require is not supported by existing survey sites, so I need to build my own. What I need is a conditional survey (if the answer to question 4 is “yes,” then we ask question 5 and skip question 6; but if the answer to question 4 is “no,” then we skip question 5 and ask question 6).
An online discussion forum is a site wherepeoplecan holdconversationsin the form of postedmessages. Discussion forums allow conversations to take place when people are not on-line, and messages may be temporarily archived. Also, depending on the forum set-up, a message might need to be approved by a moderator before it becomes visible to other users. Forums have a specific set of terms, for example, a single conversation is generally referred to as a “thread”. Threads can be replied to by as many people as would like to. Most forums require users to register and subsequently log in to post messages. However, on most forums, users do not have to log in to read existing messages.
Level:BeginnerSo many organizations face the common problem of storing employee schedules. No matter what institution: a company, a university or simply an individual, many entities need an application to view schedules. Therefore, I will try to come up with a database model and then, in a future article, we’ll talk about a simple application to store employees’ schedules in a database.Currently the design looks as follows:The model is pretty straightforward.Tip – Natural and Surrogate Key Strategies
A good data modeling exercise for beginners is to create a data model of an online store. Every time I give this exercise to my students, I’m surprised at how difficult it is for them.Find the Concepts…Let’s see how it can be done. We know we have to create a table for every concept in the domain. Think about thenouns and noun phrases you would use to describe the domain. Roughly, every noun iseither a concept, an attribute of a concept, or an example