I am quite sure that the term you are looking for is derived relation.
In relational jargon, a single and normalized table is called a base relation (or base table). In a SQL database management system (DBMS), relations (tables) of this category are the ones declared with the aid of the data definition language (DDL).
The fact of applying certain relational operations (such as JOIN, for instance) on these base relations (or base tables), yields a different type of relation, which is known as derived relation (or derived table). It may be useful to think of this kind of relations as “virtual” tables (as you properly stated) that arise as a result of “playing” with the aforementioned base ones. Tables of this class are declared with the aid of the data manipulation language (DML) in a SQL DBMS.
In order to get practical advantages, a derived relation (table) can be set up as a VIEW in a DBMS. Of course, some of them can be wide and some of them can be narrow, and this aspect is determined by the number of base relations involved and the number of attributes (columns) defined in a particular operation.
To support the above, and because you want an accepted term, it is necessary to refer to an authoritative source. The best one is, naturally, the inventor of the Relational Model, Dr. Edgar Frank Codd. Regarding to these type of relations (tables), here is a small excerpt1 from his paper Extending the Database Relational Model to Capture More Meaning:
Base relations are those which are defined independently of other relations in the database in the sense that no base relation is completely derivable (independently of time) from any other base relation(s). Derived relations are those which can be completely derived from the base relations. It is this kind of relation which is normally employed to provide users or application programs with their own views of the database.
And here is an extract2 from his book The Relational Model for Database Management: Version 2:
…[R]elations, or R-tables, that are internally represented by stored data in some implementation-defined way are called the base relations or base R-tables. All R-tables other than base R-tables are called derived relations or, synonymously, derived R-tables. An example of a derived relation is a view. A view is a virtual R-table defined in terms of other R-tables, and is represented by its defining expression only.
References
1 Codd, E. F. (Dec. 1979). Extending the database relational model to capture more meaning, ACM Transactions on Database Systems, Volume 4 Issue 4 (pp. 397-434). New York, NY, USA.
2 Codd, E. F. (Jan. 1990). Introduction to Version 2 of the Relational Model. In The Relational Model for Database Management: Version 2 (pp. 17-18). Boston, MA, USA: Addison-Wesley
There is nothing in your question that would speak against inheritance.
One major limitation of the current Postgres implementation of inheritance is that FK, PK and UNIQUE constraints only apply to individual tables, not including children. But that seems to match what you need exactly: Create PK and FK constraints on each child table individually.
nba_players
, nfl_players
etc. could conveniently inherit from a general parent table players
(possibly in a separate schema for empty parent tables). ALTER TABLE
on the parent cascades to all children (except for ALTER TABLE ... RENAME
). This makes it easy to run queries on all player tables together by targeting the parent.
If you never need to look at all player tables together or cascade changes to all tables, it might be better to just keep a template table (possibly in a separate schema for template tables) and create instances with CREATE TABLE ... (LIKE ...):
CREATE TABLE nba_players(LIKE players);
Best Answer
After spending some time, reading reference books, I came to the point, where I could define the difference between the star schema and data cubes. I could not comment on this definition but this answer satisfies me and help me to start the task. On the process, I hope I get better understanding (if exists) of these techniques. Here is my findings:
Difference between Star Schema and data cubes:
Where I have to start?
I realized I have to start from, star schema and build data cubes on it. Data cubes are not built on the DBMS system, but outside the DBMS system as aggregates and other operations.
I hope this answer will help the people who are new to this technologies. If I am missing something or understanding wrongly, please correct it. Thank you.