In a sense, logical design is what you draw with a pencil before building your warehouse and physical design is when you create the database with SQL statements.
Logical models use fully normalized entities.
The entities are linked together using relationships.
Attributes are used to describe the entities.
The UID distinguishes between one instance of an entity and another.
A graphical way of looking at the differences between logical and physical designs
Physical design is where you translate the expected schemas into actual database structures. At this time, you have to map:
- Entities to Tables
- Relationships to Foreign Keys
- Attributes to Columns
- Primary Unique Identifiers to the Primary Key
- Unique Identifiers to Unique Keys
You will have to decide whether to use a one-to-one mapping as well.
Translating your schemas into actual database structures requires creating the following:
- Tablespaces
- Partitions
- Indexes
- Constraints
Tablespaces need to be separated by differences. For example, tables should be separated from their indexes and small tables should be separated from large tables.
Partitioning large tables improves performance because each partitioned piece is more manageable. Typically, you partition based on transaction dates in a data warehouse. For example, each month. This month's worth of data can be assigned its own partition.
Data warehouses' indexes resemble OLTP indexes. An important point is that bitmap indexes are quite common.
Constraints are somewhat different in data warehouses than in OLTP environments because data integrity is reasonably ensured due to the limited sources of data and because you can check the data integrity of large files for batch loads. Not null constraints are particularly common in data warehouses.
No comments:
Post a Comment