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.
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.
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 Part 1 of this series, we successfully imported the SuiteCRM database structure into our online database modeling tool. That’s when we saw that the model contains 201 tables without relationships between them. We got a wild bunch of tables that looked really messy. In this article, I will show you how you can organize such a large model.Just after importing to Vertabelo , the SuiteCRM database model looks as follows:
Customer Relationship Management applications (CRMs) have been around for some time. We could argue a lot about their advantages and disadvantages when comparing them to each other. In this series, we’ll take a look at the database behind one popular open-source CRM: SuiteCRM. We will also show how to install everything we need. In upcoming articles, we’ll organize the data model and see how it reacts to changes made on the user interface.
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 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 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
So I’m sitting here thinking to myself: How should I share a new Vertabelo feature with you?Should I write an over-hyped article full of industry jargon and marketing-speak?Hmmm. Tried and failed.I’ve just gotta show you the feature straight up and how simple it is to use. More meat, little fat, no fluff :).Well tah dah! Here it is:Go ahead, click on it. Use the mouse wheel to zoom. Drag to pan. Click on the logo make the diagram bigger.