Free tools Windows power users keep installed
One-click scans. No signup required.
A normalized database and a star schema solve different problems. A 3NF-style operational model keeps each fact in the appropriate place to support accurate inserts and updates; an analytic star schema brings useful descriptions alongside measurable events so people can filter and summarize them. The key is to define the business question and fact-table grain first—not to duplicate data indiscriminately.
What 3NF and a star schema are designed to do
Third Normal Form (3NF) is a relational design approach that organizes data to minimize repeated facts and reduce insertion, update, and deletion anomalies. Oracle describes those as central goals of 3NF design in its discussion of data-warehouse logical design. Its documentation is from the Oracle Database 12c era, so this is a conceptual design statement, not a current product specification. Oracle: Data Warehousing Logical Design.
A star schema organizes an analytic model around two kinds of tables:
- Fact tables record business events or observations, usually with measurable values and keys linking to dimensions.
- Dimension tables describe the entities and contexts used to filter, group, and label those measures, such as dates, restaurants, or delivery areas.
Microsoft describes this fact-and-dimension pattern as a foundation for star-schema modeling in Power BI and Microsoft Fabric. Microsoft Learn: Understand star schema and the importance for Power BI; Microsoft Learn: Dimensional Modeling – Microsoft Fabric.
#1 Best Overall
- Used Book in Good Condition
These are not rival doctrines. An organization can retain a normalized operational store and build a dimensional layer for analytics. Oracle explicitly discusses 3NF and star-schema designs as complementary approaches; one layer can feed another.
Food delivery in the operational model
Imagine a normalized teaching model with separate records for customers, restaurants, menu items, delivery orders, order lines, couriers, and delivery statuses. A customer’s contact details belong with that customer; a restaurant’s address belongs with that restaurant. An order line points to a menu item and records a quantity. These are illustrative entity choices, not a prescribed schema for any real delivery company.
Keeping order-level information distinct from line-level information matters. An order may contain several items, while its delivery duration is usually a property of the order as a whole. If a query joins an order-level duration to every order line and then sums the result, the duration is counted repeatedly. Separating entities helps maintain operational records, but reporting across them may involve several relationships and joins.
Start an analytic model with the question and grain
Suppose the question is: “How do delivered item sales and delivery times vary by day, restaurant, menu item, customer segment, and delivery area?” Before choosing columns, decide what one row in each fact table represents. That level of detail is the grain.
Recommended Free Tools
Rank #3
Order-line sales grain
For item sales, one reasonable grain is one row per item line on an order. A corresponding FactOrderLine could include date, restaurant, menu-item, customer, and delivery-area keys, plus the order identifier if useful, quantity, line amount, and discount amount.
Order-level delivery grain
If delivery duration is recorded once per order, it belongs at order grain rather than being treated as a line-level measure to sum. One option is a separate order-level delivery fact; another is an aggregation rule designed for the measure and analysis. The right choice depends on the questions the model must answer.
Rank #4
Microsoft’s fact-table guidance defines grain as the atomic level represented by fact rows and explains that key values determine that granularity. A fact stored too coarsely cannot necessarily be broken into detail later. Microsoft Learn: Modeling Fact Tables in Warehouse – Microsoft Fabric.
A possible food-delivery star
For the order-line sales question, the star’s central fact table holds line-level measures and foreign keys to descriptive dimensions. The names and contents below are a design sketch, not facts about a specific platform.
| Table | Example contents | Role |
|---|---|---|
FactOrderLine |
Date, restaurant, menu-item, customer, and delivery-area keys; order identifier; quantity; line amount; discount amount | One row per item line on an order; holds measures to analyze |
DimDate |
Calendar date, weekday, month, quarter, year | Supports time-based filtering and grouping |
DimRestaurant |
Restaurant name and descriptive location or category attributes | Supports restaurant-based filtering and grouping |
DimMenuItem |
Item name and category, with restaurant context handled explicitly when item identity varies by restaurant | Supports item-based filtering and grouping |
DimCustomer |
Only attributes suitable for the reporting purpose and privacy constraints | Supports customer-based analysis where appropriate |
DimDeliveryArea |
Delivery zone and useful business rollups | Supports geographic filtering and grouping |
Dimension keys connect descriptive context to facts; measures such as quantity and line amount are summarized through that context. In a real model, decisions about status changes, refunds, cancellations, currencies, tips, taxes, and changing customer or restaurant attributes must be made explicitly. The example does not prescribe one universal treatment for those cases.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Why repeat some descriptive values?
In an operational design, a restaurant’s category or an area’s hierarchy may be stored in separate related records so it is maintained once. In an analytic dimension, relevant attributes can be brought together so a user can group or filter restaurant sales without navigating multiple small tables. That can make the model easier to understand and may help retrieval, at the cost of repeating descriptive values and maintaining them in the analytic layer.
This is the “break it on purpose” part: selected descriptive repetition can be useful in dimensions. It is not permission to copy every value everywhere, nor to ignore measure grain. A more normalized or snowflaked dimension can still be appropriate where the data, maintenance needs, or query patterns favor it. Microsoft’s Power BI guidance notes the usability benefit and storage tradeoff of denormalized dimensions; Oracle likewise treats dimensional and 3NF designs as potentially complementary.
How to choose the design for the job
| Design consideration | 3NF operational model | Star-schema analytic model |
|---|---|---|
| Primary work | Insert, update, and delete accurate operational records | Filter, group, and summarize data for analysis |
| Table organization | Distinct entities and relationships help reduce repeated facts | Facts connect to descriptive dimensions |
| Repetition | Minimize redundancy and modification anomalies | Allow selected descriptive redundancy for simpler use and retrieval |
| Typical query shape | Broad reports may traverse multiple entity relationships | Measures are analyzed through dimension filters and groupings |
| Design anchor | Data entities and their dependencies | Business process, declared grain, dimensions, and facts |
Kimball’s dimensional-modeling guidance emphasizes understanding business requirements and data realities, then identifying business processes, grain, dimensions, and facts. That makes star-schema design a way to serve analytical questions, not a mechanical conversion of every normalized table. Kimball Group: Dimensional Modeling Techniques.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →There is no evidence here for a specific query-speed percentage, storage saving, or universal performance result. The benefits depend on the data volume, users, and query needs.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
