About
A table is:
- that stores data
The name comes from the counting table (abacus) that was found in the medieval counting houses.
Type
In the data model, tables may have different purpose such as:
- Staging tables to store temporary data
- Auxiliary tables to store metadata
Management
How to list them?
You can list the tables of your database via the information schema.
Example with
- Sql Server
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'
- Sqlite, the information schema is the sqlite_master table.
SELECT name FROM sqlite_master WHERE type='table';
Creation
You can create tables via for database:
- without SQL, via their library API, they should have a create function
- with SQL support,
- with the create table statement to have only the structure
- with a create as statement to create a table from a query (ie values)
Example:
- create table statement
CREATE TABLE Employees (
EmployeeID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Department VARCHAR(50),
HireDate DATE
);
Insertion
You insert data into database table:
- without SQL, via their library API, they should have a insert function
- with SQL support, via the insert statement and the values statement.
Cardinality
The cardinality of a table is the size of a table, the name comes from the set theory.
Split For Data Loading
Even if it's possible to get all data in one table that has the same grain (unique key), you may split the columns that are not functionally related into 2 different tables
Why?
- because you get minder dependency. You can change your table and your process independently. For instance, the test data set doesn't need to be updated to reflect the changes of a second functional process.
- You can then set columns NULLABLE. If a process updates columns after another one (such as total calculation), the value of this column is unknown for the first process and the columns must be set as not nullable.
- You can add audit columns by process (Update and create (date|user))
Why not?
- You may get consistency problems. There may be not a one-to-one relationship. But as the two functional processes are independent, it may be not always needed.
- Performance: You add a join cost
Model as stream
A table is a stream of:
- data manipulation:
- with an infinite windows
- that you will find persisted in a write-ahead log (queue)
1)
Image comes from https://jamesbshannon.com/2014/12/16/the-medieval-calculator/