star schema

When designing a Data Warehouse to support OLAP and Decision Support Systems the preferred database table layout is referred to as a 'Star Schema'.

This usually organises the database tables into one or more 'Fact' tables and a number of related 'Classification' table:

• The Fact tables contain the relevant transaction data (for example Sales Transactions and dates, etc), along with foreign keys to the various ways of categorising the data (for example Store, Salesperson, Area, Product).
• The Classification Tables contain the details of how the transactional data can be rolled up across the various Categories (such as City, State, Region; or Salesperson, Manager, Store, Regional Manager; or Product, Product Category, Product Line). The resulting database diagrams fall naturally into a star shape with the 'Fact' tables in the centre and the 'Classification' tables clustered around them, which is where the term "Star Schema' comes from.

Using this structure, it is easy to provide quick lookups based on a selectable list presented to a user. It is also the structure best suited to building cubes that pre-summarise data records in the 'Fact' tables across the all the different combinations of levels in the 'Classification' tables.