Does anybody use star-schema databases to collect and organize their data?

Question

I've been reading about the star schema (or dimensional) database structure, which puts all measurements main in 'facts' table, and all context for those measurements in 'dimension' table linked to the facts table (I'm doing a horrible job explaining this), so that you can query any measurement by conditioning on the dimension.

When I read about this, it struck me as a perfect way to aggregate different csv files with different columns into one store... for example, stock, commodities, and options data in various files go be shoved into a star-schema db with a facts table containing the open/close/high/low/volume, with foreign-key links to dimensions such as time, company, exchange, and 'file pedigree' so that we know where each entry comes from. That would allow you to query for, say, first quarter chinese mining companies with a single, fairly simple sql statement.

However, all of the literature on this topic seems to be for large scale 'enterprise' deployments and not small data analysis or research jobs. Does anyone have any experience organizing their data this way? Is there are simpler way?

Answer 1

From the simplest to the most complex, there are several ways of designing the schema of a database.

With the "single table schema", you just put everything in a single table. It is the easiest to use. The main drawback is that there is no guarantee that the data is consistent: for instance, I once had a table with a "country" column and different spellings for the same country. Another problem, caused by those redundancies, is the waste of disk or memory space, but if your database is a column store, it will notice those redundancies and compress the data accordingly -- for that type of data, the performance can be one or two orders of magnitude better than standard (row-based) databases.

The "star schema" is almost the same thing, but with the redundancies moved to other tables. The main table ("fact table") remains the largest, and is linked to the new tables ("dimension tables": client, product, country, date, etc.) Since there are no links between the dimension tables the structure of the database remains simple and manageable (if you draw it, it looks like a star). There may still be some redundancies (the database is not in normal form), if they cannot be removed without complexifying the schema.

The most complex type of schema would be a normalized database, i.e., intuitively, a database with no redundancies whatsoever (there are various levels of normalization, but if you only use data, those distinctions do not really matter). The links ("foreign key constraints") between the tables can be arbitrarily complex -- if you draw them, this looks like spaghetti.

For data analysis, I usually try to stick to the 1-table model: you do not have to think about the structure of the data. In some cases, this poses problems, and a star schema may be preferable. This is the case, for instance, if the data comes from different sources, each source corresponding to a dimension table.

Unless you have a database administrator to manage the data for you and build views to hide the complexity, you should stay away from more complicated schemas: they are cleaner, but more difficult to maintain and use -- they would quickly fall into disuse.

Answer 2

This sounds close to the normal forms of relational databases, which I have heard of, and used, but I haven't heard of the star schema before. Having worked with multiple terrabyte size data warehouses in the past, the fourth normal form is awful for data analysis as one spends half one's time trying to work out what tables one needs data from, and then having to merge parts of lots of tables.

They work so long as

1. the business users set how they want the tables, not the database administrator, and
2. when you have lots of transactional data that repeats over time, e.g. claims, police offences, welfare histories, personnel histories, and
3. there are schema that show how the tables relate together, along with detailed table and field descriptions in documentation, and
4. there are business rule definitions for data use (e.g. when a welfare benefit spell restarts within 2 weeks, count as one benefit spell rather than two).

If you're doing one-off studies, or you have relatively small amounts of data, these are overkill. These types of databases are stored in huge data warehouses, such as that offered by IBM, SAS, Oracle, and SAP. These require dedicated resources to develop and maintain, and the staff require specialist qualifications. There is no point going to this level of cost and effort for simple databases, and possibly no point even going to a relatively simple option like MS Access, because of the sunk cost of getting one up and running combined with the analyst time costs of then having to extract the information in usable format.