The Universal Database Design An Equal Format Primer

The Universal Database Design An Equal Format Primer The Future of Data For Database Architects

Problems of Relational Table Design: Entities and Relationships Primary Entity Key Relationship Type Secondary Entity Key (Fan 23) Type Of (Acme. Fan) (Fan 23) Contains (Motor 23) (Fan 23) Connected To (Wire 14) Fan Table Motor Table Wire Table q Primary / Secondary Fans Motors Wires Fans X X X Motors X X X Wires X X X To handle objects effectively, you need to be able to relate any entity to any other entity, but this is difficult when they are separated into different entity tables. q This chart shows that to relate the entities of an N=3 number of tables to each other, there are N 2=9 number of combinations – a measure of complexity.

Problems of Relational Table Design: Entities and Attributes Entity. Key Name Manuf (Acme. Fan) Acme Fan Acme (Fan 23) Fan 23 Acme (Motor 23) Motor 23 Nadir (Wire 14) Wire 14 Nadir Length Unit Size 13 in Small 10 ft Size Unit X X Unit 15 k. W 30 % 20 mm 2 0. 001 Ω q Commonly-used attributes and relationships are simpler to manage if the entities using them are put into the same entity table. q Allowing different input formats and units of measurement also increases the amount of unused space. q But putting different types of entities into the same table increases the amount of unused space. q To allow more than one value per cell requires either more columns or more secondary tables.

Problems of Relational Table Design: System Complexity q Because database tables are customdesigned and crafted to match the applications they support, they are isolated from each other. q q Integration leads to an N 2 number of point-to-point translations and increased developmental gridlock. Because most changes are structural, it increases the cost of change, including the transition (or loss) of legacy data.

Solution 1 to Relational Table Design: The General Relation Table q To handle object data and other types, we need to increase the flexibility of relationships, but without increasing the complexity. q As shown previously, complexity of a system is proportional to the number of different types of tables squared(N 2). To reduce the complexity, it is very effective to reduce the number of table types. q A solution starts with defining a General Relation Table to handle any standard relationship between two entities. Primary Entity Key Relationship Type Key Secondary Entity Key (Fan 23) (Type. Of) (Acme. Fan) (Fan 23) (Contains) (Motor 23) (Fan 23) (Connected. To) (Wire 14) q The General Relation Table uses foreign keys from a General Entity Table holding the entity and relationship types.

Solution 2 to Relational Table Design: The General Entity Table q A General Relation Table requires entities and relationship types to be from the same table, the General Entity Table. q Combining all entities in the same table lets any entity have any attribute, but generates more unused space as is. Entity Key Name Manuf (Acme. Fan) Acme Fan Acme (Fan 23) Fan 23 Acme (Motor 23) Motor 23 Nadir (Wire 14) Wire 14 Nadir (Type. Of) Type Of (Contains) Contains (Connected. To) Connected To Length Unit Size 13 in Small 10 ft Size Unit X X Unit 15 k. W 30 % 20 mm 2 0. 001 Ω

Solution 3 to Relational Table Design: Convert Attributes to Relations q Using normalization, attribute columns can be replaced with relationships, eliminating the empty space, and allowing multiple values for each attribute. Primary Entity Key Relationship Type Key Secondary Entity Key Name (Acme. Fan) Acme Fan (Fan 23) (Type. Of) (Acme. Fan) (Fan 23) Fan 23 (Fan 23) (Contains) (Motor 23) Motor 23 (Fan 23) (Connected. To) (Wire 14) Wire 14 (Acme. Fan) (Manuf) (Acme) (Type. Of) Type Of (Fan 23) (Manuf) (Acme) (Contains) Contains (Motor 23) (Manuf) (Nadir) (Wire 14) (Manuf) (Nadir) (Connected. To) Connected To (Manuf) Manuf

Equal Format Databases: A Different Type of Container q q The old design pattern holds values whose meaning are dependent on the row and column positions in each table. This leads to separate, hardcoded, custom-built functions to handle each different table and column. The new design pattern holds small, atomic pieces of Equal Format Data in a standard container, called an Equal Format Database. This leads to standard, reusable functions with powerful data management capabilities across all “columns”.

Equal Format Databases: A Different Way of Normalizing q To see the differences in normalization patterns, first examine the statistics of the entities and attributes used. Fans Motors Old Motor Table Wires Wire Table q The old design method uses the pattern of data to define separate tables that match the groupings, each with some of the possible attributes. Ne w Fan Table q The new design method keeps everything in the same “table”, with a full selection of all possible attributes – past, present and future.

Equal Format Databases: Semantic Triplets q Entity-Relationship (E-R) Triplets have some limitations. They can be difficult to use for some data. It would help to expand past triplets, but we need a larger pattern to guide us. Subject Key Verb Key Object Key (Fan 23) (Type. Of) (Acme. Fan) (Fan 23) (Contains) (Motor 23) (Fan 23) (Connected. To) (Wire 14) (Acme. Fan) (Manuf) (Acme) (Fan 23) (Manuf) (Acme) (Motor 23) (Manuf) (Nadir) (Wire 14) (Manuf) (Nadir) q The first step in improving triplets is to convert the entity-relationship triplets to Semantic Triplets. This only requires a name change to the columns, making each row into a Sentence. This concept lays the foundation of the next steps.

Equal Format Databases: Beyond Triplets Sentence Key Syntax Key Phrase Key (S 1) (Subject) (Fan 23) (S 1) (Verb) (Type. Of) (S 1) (Object) (Acme. Fan) (S 2) (Subject) (Fan 23) (S 2) (Verb) (Contains) (S 2) (Object) (Motor 23) (Subject) (Fan 23) (S 3) (Verb) (Connected. To) (S 3) (Object) (Wire 14) (Subject) (Acme. Fan) (S 4) (Verb) (Manuf) (S 4) (Object) (Acme) q Another normalization step moves each phrase to its own row, allowing more phrases per sentence and more phrases per syntax position. q Now you can scan through “tables” and “columns” as easily as rows. q Controls can be added for special capabilities, such as nondestructive changes, point-in-time storage, with less locking problems.

Equal Format Databases: Simpler System Designs q q The old design pattern leads to an N 2 number of point-to-point translations, leading to developmental gridlock. The new design pattern reduces the burden of point-to-point translations, providing a uniform container, a uniform pipeline, and simpler systems.

Equal Format Databases: Scalable System Designs q q Built on standard database systems, Equal Format Databases are easily integrated with legacy data in various ways. Equal Format Database containers can be grouped together to take advantage of many scaling techniques, without altering the data atoms moved between them.