The right way to model multiple FACTs schema Background
I'm in a process of designing a database (using a STAR schema).
There are three tables to model: products, tests, states.
The database will be used to store results of tests conducted on products (in a great simplification). There can be many tests pointing out to a single product but each test is unique (they are not shared among products). Besides, I need to record the current state of the product, at the time when the test was conducted. Let's assume that the state of a product describes its current location and owner, which are changing very often. That will most likely involve SCD lvl 2 - to track the history of state changes and to be able to locate a product with all its tests as well as the states it had during these tests.
Problem
I'm not entirely sure how to model this problem. It seems obvious to store every test in a FACT table. This table would then consist of thousands of transactions. On the other hand, there will also be hundreds (and later thousands) of products, so I should probably keep them in a second FACT table. Then, there will also be thousands of state changes, so in order to record their entire history, I would need to keep them in a ... FACT table as well? I've been told that FACT tables are typically used to store multiple-rows data but on the other hand where are the DIMs in this model?
I also don't know how to model the relationships between these tables. Products - states is a 1:* relationship. Products - tests is a 1:* as well. Finally, states - tests is a 1:* too. I would then go with linking products to states and then states to tests (products 1&lt;-* states 1&lt;-* tests), what would allow me to find all states for a particular product and all tests (in all states or in a selected state). What do you think about that? The problem here is that, as I keep adding states, I have two options: either keep duplicating products in the products table (with added &quot;recorded_timestamp&quot; column) or use a SCD lvl 2 in states table, pointing out to the products table with a FK, but this would effectively make the product table a DIM!
Any help here would be very appreciated.
So the first question is: Are you absolutely sure you need to use a star schema?  Having worked in a number of warehouses built to varying levels of quality, I have found star schema to be: 1. Inflexible 2. Difficult to use (tons of joins) 3. Really bad for time series.
The way you have described things (Product, Product State, Test) along with the need to track points in time would generate something like this (slight embellishment for party/test):

If you need to get the state as of a certain test this is attainable by a simple join, which can be encapsulated in a view so users don't have to worry about getting the logic correct:
SELECT
  ProductTest.ProductId
 ,ProductTest.TestDtm
 ,ProductTest.TestCd
 ,&lt;Other relevant columns&gt;
 ,ProductState.OwnerId
 ,ProductState.&lt;Location info&gt; --Don't know if this is address or lat/long, use whatever works best for you
FROM
  ProductTest ProductTest
LEFT JOIN
  ProductState ProductState
    ON ProductState.ProductId = ProductTest.ProductId
         AND ProductState.UpdateDtm =
               (
                 SELECT
                   MAX(UpdateDtm)
                 FROM
                   ProductState
                 WHERE
                   ProductId = ProductTest.ProductId
                     AND UpdateDtm &lt;= ProductTest.TestDtm
               )