In this lesson, you will learn how to bridge the gap between human language and database architecture by extracting primary business entities from narrative requirements.
When a stakeholder describes their business process, they are providing the blueprint for your data schema. Before you worry about columns or data types, you must identify the entities—the "things" that your system needs to store information about.
The Extraction Technique
To identify entities, look for the nouns in the business requirements. These usually represent people, objects, places, or events that exist independently.
Example Scenario: “A customer logs into our portal to place an order. Each order contains multiple products from our inventory.”
If we break this narrative down, we can extract three distinct entities:
- Customer: The person initiating the activity.
- Order: The event or transaction being recorded.
- Product: The specific items being tracked in inventory.
Mapping to SQL
Once identified, these entities often become the tables in your relational schema. You are essentially creating a digital representation of the real-world nouns you just extracted. While a full schema requires defining relationships, your first step is simply defining the existence of these entities:
-- Creating tables based on identified entities
CREATE TABLE Customers (CustomerID INT PRIMARY KEY);
CREATE TABLE Orders (OrderID INT PRIMARY KEY);
CREATE TABLE Products (ProductID INT PRIMARY KEY);
Filtering the Noise
Not every noun is an entity. Be careful to distinguish between entities (the "what") and attributes (the "properties"). For example, "Customer Name" or "Order Date" are pieces of information about an entity, not entities themselves. If you try to make "Date" a table, you’ll end up with a messy, non-relational structure.
Focus on identifying the core participants in the business process first. If a noun can be described by several properties but doesn't independently represent the primary subject of a transaction, it is likely an attribute rather than a core entity.
Key Takeaway: To build a robust database schema, translate business narratives into SQL by isolating the nouns that represent distinct, independent objects, treating them as your primary entities while setting aside properties as attributes.