SQL - Table

1 - About

A table is a SQL object that shows a relational data structure that stores data (whereas a query is a relation that does not store data)

It seems to comes from the counting table (abacus) in the medieval counting houses.

3 - Type

Slightly outside of the tables in the data model, we have other kinds of tables:

  • Staging tables bring in “dirty data” so we can scrub it and then insert it into base tables.
  • Auxiliary tables hold static data for use in the system, acting as the relational replacement for computations.

4 - Management

4.1 - List

4.2 - Creation

  • Sql:
    • SQL - CREATE TABLE - create the structure
    • SQL - VALUES - create a table from values

4.3 - Cardinality

The cardinality of a table is the size of a table, the name comes from the set theory.

4.4 - Split

Even if it's possible to get all data in one table that has the same grain (unique key), you may made two tables to split the columns that are not functional related.

Why ?

  • because you get minder dependency. You can changes your table and your process independently. For instance, the test data set don'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 an other one (such as total calculation), the value of this column are unknown for the first process and the columns must be set not nullable.
  • You can add audit columns by process (Update and create (date|user))

Why not ?

  • You may got consistency problems. There may be not a one-to-one relationship. But as the two functional processes are independent, it's may be not always needed.
  • Performance: You add a join cost

5 - Documentation / Reference

Data Science
Data Analysis
Data Science
Linear Algebra Mathematics

Powered by ComboStrap