Star Joins

You can picture a star join as a structure with many branches, in which each branch is directly related to one table in the middle.

In the Hierarchy of a join relationship figure, Tables D, F, and E form a very simple star join. More commonly, Table F would be at the center of many tables, each of which is joined to Table F.

In a star join, multiple tables are related to one table at the center of the join, in a one-to-many relationship. The one table at the center of the join represents the “many” side of the relationship, while each of the tables around it represent the “one” side of the relationship. Each table on the “one” side holds a set of values with its own unique primary key. A foreign key in the table on the “many” side of the relationship relates that table to the primary key of the table on the “one” side of the relationship.

The “many” table at the center of the star is sometimes called the fact table. The “one” tables related to it are called the dimension tables.

Example

In the following example, the SalesOrders table contains three foreign keys, each of which is related to the primary key of another table.

Shown is relationship of the sales underscore order table to the three foreign keys (customer, employee and fin code) each of which is related to the primary key of another table

You can create this table using the following commands:

CREATE TABLE GROUPO.SalesOrders (
   ID NUMERIC (4) NOT NULL IQ UNIQUE (648),
   CustomerID INTEGER NOT NULL IQ UNIQUE (109),
   OrderDate date NOT NULL IQ UNIQUE (376),
   FinancialCode CHAR (2) NULL IQ UNIQUE (1),
   Region CHAR (7) NULL IQ UNIQUE (5),
   SalesRepresentative  INTEGER 
   NOT NULL IQ UNIQUE (11)
);

COMMENT ON TABLE GROUPO.SalesOrders is 
'sales orders that customers have submitted 
to the sporting goods company';

ALTER TABLE GROUPO.SalesOrders
ADD FOREIGN KEY FK_CustomerID_ID (CustomerID)
REFERENCES GROUPO.Customers (ID)
ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE GROUPO.SalesOrders
ADD FOREIGN KEY FK_FinancialCode_Code (FinancialCode)
REFERENCES GROUPO.FinancialCodes (Code)
ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE GROUPO.SalesOrders
ADD FOREIGN KEY
FK_SalesRepresentative_EmployeeID
(SalesRepresentative)
REFERENCES GROUPO.Employees (EmployeeID)
ON DELETE RESTRICT ON UPDATE RESTRICT;

As shown in the figure, the Salesorders table is at the center of the star join. Each of its foreign key columns can contain many instances of the primary key it refers to. For example, if you enter:

SELECT SalesRepresentative FROM SalesOrders
WHERE SalesRepresentative = 299

the results show 114 rows with 299 in the SalesRepresentative column.

However, if you enter:

SELECT EmployeeID FROM Employees 
WHERE EmployeeID = 299

the results show only one row with 299 in the EmployeeID column.

Note: Query optimizations for all joins rely heavily on underlying primary keys. They do not require foreign keys. However, you can benefit from using foreign keys. Sybase IQ enforces foreign keys if you set up your loads to check for primary key-foreign key relationships.

Sybase IQ does not support star-join style join indexes that use multiple join key columns for any given join.

For a true star join (that is, one in which none of the dimensions shares a join key with any other dimension), the IQ query optimizer allows a maximum of 24 dimension tables in a single clause. However, as the time required to process the query increases exponentially with the number of dimensions, performance degrades as you get close to this maximum.