Business Objects and Rules

As covered in Database Design, the first course in this series, one of the earliest steps in designing a relational database is identifying which business rules actually need to be represented in the database itself — as opposed to enforced somewhere else entirely. This lesson works through the mistakes designers commonly make on both sides of that identification process: getting the objects wrong, and getting the rules wrong.

We'll use a running example throughout: Stories on CD, a company that sells CDs it orders from distributors. Despite everything Stories on CD does — managing inventory, tracking orders, dealing with distributor relationships — its database only needs four business objects^[1] to represent all of it:

1. CDs
2. Categories
3. Distributors, and
4. Orders

That's a small list for a real, functioning business — and that's exactly the point of this lesson. Design mistakes involving business objects generally come from one of two directions: either cramming multiple objects into a single table, or the opposite mistake of adding more objects and attributes than the business actually needs.

Perhaps the most fundamental mistake in table design is representing more than one business object in a single table. It's tempting, for instance, to store a CD's distributor's name and address right alongside the CD's own information — one table, everything about the CD in one place. It's also a mistake. Distributor information belongs in its own table, linked to CDs through a primary key/foreign key pair, not folded into the CD table itself.

The reason this matters goes well beyond tidiness. When two distinct business objects share a table, every attribute belonging to one of them gets duplicated across every row that references it. If Stories on CD orders from the same distributor a hundred times, that distributor's name and address are now sitting in the database a hundred separate times — and duplication like that doesn't just waste space, it actively creates three specific, well-understood failure modes:

• Update anomalies — if that distributor moves to a new address, every single duplicated row needs updating. Miss even one, and the database now contains two different addresses for the same distributor, with no way to tell which one is current.
• Insertion anomalies — you can't add a new distributor to the database at all until you have at least one CD order to attach it to, since the distributor's information only exists as part of a CD row.
• Deletion anomalies — if that one CD order gets deleted, the distributor's information vanishes along with it, even though the distributor itself still very much exists.

The fix is normalization — specifically, applying Second and Third Normal Form to pull each distinct object into its own table. Separate CDs from Distributors, link them with a foreign key, and every one of those three anomalies disappears, because a distributor's information now lives in exactly one place, updated in exactly one place, independent of how many CD orders reference it.

Worth remembering: just because a business object can be turned into a database table doesn't mean it should be. Not every concept the business talks about needs to become a table — which brings us to the opposite mistake.

The mirror-image mistake is including too many objects, or too many attributes of an object, in the database. It's worth remembering that a database is a representation of reality, not an exact replica of it — and representations are supposed to leave things out.

Take Stories on CD's employees. It might seem natural to add an Employees object to this database, tracking who placed each customer's order. But suppose the owners decide that's not information they actually need — a customer's order history doesn't need to know which staff member processed it. If they later change their minds, that table can always be created then. Or, if an Employees table already exists elsewhere on the same system, managed by the same database engine, it may well be possible to reference that existing table from this database's views and queries instead of duplicating it here.

The same overreach happens at the attribute level, not just the object level. It might feel useful to record which recording studio produced each CD — but capturing that detail for every single CD is real, ongoing data-entry work for very little practical benefit to Stories on CD's actual business.

Beyond just being unnecessary, overstuffed tables carry real technical costs of their own:

• Performance bottlenecks — tables with dozens or hundreds of columns force the database to read larger data pages off disk and hold more in memory, even for a query that only needs two or three of those columns.
• Sparse data — attributes that only apply in specific cases (like a recording studio name that only some CDs have) tend to leave a table littered with NULL values, wasting storage and forcing every query against that table to account for missing data.
• Locking contention — in a busy, transactional system, cramming unrelated attributes into one wide table means updates to genuinely unrelated fields can end up blocking each other, simply because they live in the same row.

When a table's attribute list is genuinely growing too wide, vertical partitioning is the fix: keep the core, frequently-accessed attributes in the primary table, and break optional, specialized, or rarely-needed data out into a related table connected by a one-to-one relationship. A CD's core catalog information stays in CDs; a rarely-needed detail like recording studio, if it's kept at all, can live in a separate, optional table instead of bloating every row of the main one.

Objects aren't the only thing designers get wrong — business rules^[2] cause their own category of mistake, usually in the form of trying to enforce something in the database that the database genuinely can't enforce well.

Take a Stories on CD policy stated as: "We only sell CDs created for children ages 14 and younger." On paper, that sounds like exactly the kind of thing a database constraint should handle. In practice, it's a poor fit: different distributors use different, inconsistent coding systems to indicate a CD's target age group, so there's no reliable, uniform data field the database could check that rule against. Trying to force it in anyway means either rejecting valid CDs because a distributor's coding doesn't map cleanly, or silently accepting CDs that don't actually meet the policy. Often, the better answer is to trust the human placing the order to apply judgment about whether a given CD fits Stories on CD's target audience, rather than trying to encode that judgment as a rigid rule.

That example points to a more general test worth applying to any business rule before deciding to enforce it as a database constraint. A rule is a good candidate for enforcement in the database if it:

1. Helps preserve data integrity, and
2. Doesn't undermine the company's potential growth in a way that would eventually require a schema revision to fix.

Rules that fail either test — ones too fuzzy to check reliably, or ones so specific to today's business that they'll need to be redesigned the moment the business changes — are usually better left to application logic, documentation, or human judgment, rather than baked permanently into the schema.

Before moving on to the next lesson, click the Quiz link below to reinforce your understanding of common database design mistakes.
The next lesson describes mistakes associated with columns.
Business Objects-Quiz

[1] Business objects: Items used in a business environment which are related and contain stored data with respect to the business process (customers, products, orders).

[2] Business rules: A set of rules or conditions describing the business policies that apply to the data stored on a company's database and tables.