Collection of Database Concepts

I will use this section to add bits of assorted information related to databases and with time I will revisit this section to add more mini-topics.


In a big organization what are the chances that groups or individuals would create views with the same name? It happens very often, for example let’s say there are separate divisions operating under a parent company selling products in completely different category and market. The business lines will obviously have their own strategies, processes and the way they do their daily business in every aspect while making sure their fundamentals are aligned with the parent company’s goal. IT resources from both divisions working on sales data from the parent company’s database will most likely create logical views or macros to support their line of business and the chances are that out of hundreds of views, some will be named the same. To avoid this scenario, RMDBS provides a hierarchical cataloging of views/tables/procedures by their schema names. The database admin can create a schema for any user or group and the schemas can have views with the same name. This helps in identifying the right entity and provides flexibility in designing and working on multiple applications by multiple groups in a company. The views would be used by the schema name followed by the view name. For ex schema1.inventory, schema2.inventory.

So in short a schema is basically a space for different users or groups with separate access privileges and gives the ability to store, run or edit views or stored procedures.

Joining Fact Tables -

Each business process translates into a dimensional table containing related contextual data based on the similar category of description. For example a sales model can have a fact table containing the measures of different quantities of unit sold with a bunch of dimensional tables surrounding this fact table. There can be a location table that would contain information of the retailers who sold the unit quantities; this information would contain retailer code, name, address, zip code, store open indicator, active flag and so on. Another table would be sales calendar containing the company sales calendar with different groupings by, say for example, week end dates, month, quarter, year etc.  Each of these dimensional tables would have a primary key through which they can be joined on a many to one relationship with the fact table.  In the simplest form a fact is surrounded by a number of dimensional tables joined through a key. This allows the query engine to pick up the data requested by the constraints and scan the fact table for related entries.