Understanding Business Rules and Objectives

You can create a beautifully appropriate database model with clearly defined objects, using a set of clearly defined database design methodological steps. Elegant solutions, however, must be practical and useful. You must understand that you are designing for practical application, not elegance, and the most elegant solution is not always the most practical. The importance of understanding the nature of the business is paramount to understanding how best to model that business in a database.

1. What Are Business Rules?

Business rules are simply the "how-to" guide for an organization—they define every action, decision, and process that keeps the company running day-to-day, from answering customer calls to shipping products. These rules directly answer core questions like what the company does, why it exists, and how it earns money, all with the goal of being profitable. However, because markets, technology, and customer needs constantly change, these rules are never permanent; they can and should be continuously redefined and improved, even within the same company, to stay efficient, competitive, and successful.

business rules cover the following aspects of an organization:

Simple business rules can be implemented in a database model by creating relationships between tables.

Other business rules can be implemented and enforced using coding and other specialized constraint types. Coding implies programming code, in whatever language is appropriate. Constraints are things that place restrictions on values stored in a database. For example, field values in table records can be validated for each entry. Only allowing a field to take on the value M or F to represent Male or Female is another example of business rule application because there is no such gender that is not male or female.

2. The Importance of Business Rules

I have had conversations with managers along the lines of, “no business rules in the database please,” or, “as many business rules in the database as possible please.”

while a relational database inherently enforces basic business rules through table relationships and field constraints (like preventing NULL values), managers who demand "all" or "no" rules in the database are missing the point—complex validations often require stored procedures, but overusing them, especially with triggers, kills performance and is strongly advised against. Instead, the author recommends keeping complex business logic in the application layer, reserving the database for data integrity, and noting that while object databases handle code-as-methods well due to their "black box" nature, that approach doesn't suit relational systems.

3. Incorporating the Human Factor

you cannot design a successful database model in isolation—you absolutely must involve the actual people who will use it, whether they are technical developers or non-technical business executives. While experienced designers often rely on past knowledge from similar companies, every organization has unique exceptions that no amount of prior experience can predict. The only reliable way to uncover those unique needs is to talk directly to the company's personnel, listen carefully to both developers and end-users, and learn from them how the business truly operates. This human-centered approach, based on active conversation and listening, is what ultimately reveals the real requirements and leads to a better, more accurate database design.

4. People as a Resource

In fact, even though I keep stressing the point of listening (always assuming the customer is right), essentially the users are people who you (the designer) gathers information from as to how the model should be designed and what should be in it—even down to the size and datatypes of fields in tables.

Ultimately, you (the designer) are 95 percent responsible for figuring out the design and building the database model. The end-users can give you many small pointers. From their perspective, it all makes sense. From your perspective, information from end-users can often feel like a stream of jumbled-up facts. It is your job to un-jumble everything and to make complete sense of it. Take time to think. Remember, design is largely a planning process, and thinking about various options is a big part of that planning process.

while end-users and employees are invaluable for providing real-world details about their specific tasks, priorities, and daily processes, the database designer must remember that users see things subjectively and often through a narrow, case-specific lens—they may view selling a Ford and a Chevy as completely different, for example. The designer's true responsibility, however, is to remain objective, analytical, and clinical, transforming that "jumbled stream" of user input into a clean, logical, and mathematical abstraction that finds common patterns beneath the surface. This abstraction process compresses different activities into generic compartments (like treating both cars simply as "automobiles") to create an efficient, flexible model. Ultimately, the designer has the final say and must listen to everything but not get sidetracked by misleading specifics, always keeping in mind that a good database model accommodates special circumstances while remaining abstract enough to avoid becoming an overly complex, wall-covering nightmare.

5. Talking to the Right People

To gather the right requirements, a database designer must talk to a balanced mix of people across different levels and roles, not just the most technically aware. In small companies, executives are accessible and provide a clear global view, while in larger firms, high-level managers with a broad operational picture are more practical entry points, though you should also seek out floor-level employees who handle nitty-gritty details to avoid missing crucial specifics. The complexity of the business dictates how many people you need to consult—a simple retailer might need very few, whereas a custom manufacturer requires many—and the goal is always abstraction: consolidating diverse special cases into a single, simple set of tables rather than creating separate tables for every scenario, which defeats the purpose of computerization. While technical staff like programmers and DBAs can offer valuable input, they can also be obstructive or out of touch with end-user needs, whereas outside consultants often bring a fresh, unbiased perspective. Ultimately, the designer must navigate this human landscape carefully, talking to enough people to get a complete picture without getting overwhelmed, all while keeping simplicity through abstraction as the central objective.

6. Getting the Right Information

Getting the right information is less about technical expertise and more about navigating human dynamics, politics, and communication to extract useful knowledge from the right people. As an outsider, a consultant often has an advantage because they can cross departmental boundaries without getting entangled in internal red tape, and their higher cost can grant them access to senior management, making them more effective; however, in-house staff can also be the best option in many cases. The key is to talk to a broad range of people across different levels and functions to gain both a bird's-eye view and ground-level details, but you must strike a careful balance—talking to too many can lead to confusion and upset those whose suggestions you don't implement, while over-abstraction can make the model unusable for special cases. Never assume you know more than the employees, but also recognize that your experience brings value, and the goal is to blend your skills with their knowledge to create a correct, usable design that people actually accept. Ultimately, success depends on listening, learning, asking for missing information, and selling yourself and your ideas with humility and tact, because being liked and ensuring enthusiastic adoption is just as important as technical correctness—and you must remember that introducing change can provoke resistance, so work around it gracefully rather than bulldozing through.

Dealing with Unfavorable Scenarios

The ideal environment for database design is often a non-computerized one, like a paper-based system, because it forces you to start fresh without the baggage of bad legacy designs. However, you will frequently encounter "ugly" scenarios involving difficult people or messy existing systems, though some employees will be genuinely eager to help fix the problems. The key strategy for any conversion—whether from a mainframe, spreadsheets, or paper—is to first clearly define what the new database model is supposed to achieve, and then work backward from that goal, gathering as much information as possible about the current system to ensure the new design meets its intended purpose.

1. Computerizing a Pile of Papers

Designing a database from a paper-based system can be either the easiest or most difficult task, depending on the system's quality. If the paper trail is meticulously designed and detailed, it clearly shows exactly how the database should be structured, having evolved over years to cover all eventualities. However, if the paper system has grown chaotically out of necessity, you'll face a nightmare of duplicated, conflicting, and messy documents, though you can still extract basic table structures and operational flows from it. In extreme confusion, your best approach is to find a knowledgeable insider who understands both the paper system and the business operations—they can help you categorize materials, verify your design, and even point out special cases you'd miss as an outsider, while also ensuring smooth handover later. Choose this person carefully with guidance from the executive who hired you to avoid political pitfalls, but remember that the company is likely eager to help because they desperately want to escape the burden of all those pesky papers.

2. Converting Legacy Databases

Converting legacy databases is often the most difficult design task because these systems can be partially inaccessible, poorly documented, or built on outdated network or hierarchical models that are large, complex, and hard to decipher. As with a paper system, your best first step is to find an in-house expert who knows the database inside out, as they can save you enormous time and effort. If no such person exists, you must dig into the database yourself, allow ample time for analysis, verify the structure, and even examine the applications to confirm what the system actually does—keeping in mind that the legacy system may have been incorrectly built from the start or no longer meets current requirements. Ultimately, whether the changes are obvious from the data, the applications, or company operations, talking to people and asking questions remains the fastest and most effective way to uncover the truth and move forward.

3. Homogenous Integration of Heterogeneous Databases

In database terms, a heterogeneous system is one that consists of dissimilar, multiple types of databases—for example, a mix of legacy network databases, hierarchical databases, relational databases from different vendors, or even flat-file systems—all operating together, while a homogeneous system is the opposite, being uniform throughout with similar or identical parts. Some sophisticated and expensive database engines allow for the homogenous integration of heterogeneous databases, meaning they can create a seamless, transparent interface that lets a controlling database retrieve and manage data from various underlying databases using specialized drivers called gateways. However, these gateways typically only support common databases, and connecting to older network or hierarchical systems often requires manual coding, which is complicated and usually not worth the development effort. Ultimately, all software has a fixed life cycle, and when legacy systems become too costly to maintain or easy to replace, there is no reason to keep them—especially since finding skilled people to maintain outdated systems becomes increasingly difficult over time.

4. Converting from Spreadsheets

Spreadsheets can be deceptively tricky because while they initially appear to be simple flat files, deeper inspection often reveals complex formulas across multiple levels, interdependencies between multiple sheets, and intricate logic built by whoever originally created them. Converting a spreadsheet into a database model is generally less complex than tackling a mainframe legacy network database or a messy paper-based system, largely because spreadsheets tend to have limited historical depth and are often recreated or lost due to human error like accidental deletions or hardware failures. However, they still require thorough analysis, clear requirements specifications for what the new database should achieve, and ideally the help of the person who built the spreadsheet to untangle any hidden complexities. Ultimately, while spreadsheets are unlikely to present the massive historical baggage of older systems, they can still harbor surprising layers of complexity that demand careful attention.

5. Sorting Out a Messed-up Database

Sorting out a messed-up database implies that there is a relational database in existence, but that the database model is a complete mess. Expect to find invalid data, orphaned records, and other such wonderful problems. Once again, establish what is needed first before starting to go through it willy-nilly. After you establish what the records are supposed to look like, you might even find that there are only a few minor structural errors or relationship errors that can be easily repaired.

Even though a task like this can seem daunting, it really only has two very distinct steps. First, establish and build the correct structure. If the company has decided that the existing structure is problematic, the company probably has plenty of ideas on how to fix it. The company probably also knows who can give you all the correct information. Second, copy data across to new tables, if necessary.

Powered by Forestry.md