Navigating Database Design Challenges: A Guide to Entity Documentation Standards

1. Identifying Entities

2. Houston we have a problem! • Database design left very late in the process • Not less important than other aspects • New module and time not on our side • Typically where it always gets landed in the process! • It makes sense to work through each layer in turn • There is a lot to think about here that is both complex and subtle

3. Document Standards • Problems compounded by differing documentation standards • Chen notation (Peter Chen 1976) • Crows Foot Notation • UML • Entity Relationship Diagrams an afterthought in UML? • (Relational v Object Oriented Databases)

4. House Site M 1 On The Chen Notation • A house must be on a site • A site may or may not have many houses • The dots indicate the membership class of related entities

5. House Site The Crows Foot Notation • No indication of membership class

6. House Site 1 0..* UML Notation • Again no indication of membership class • Pretty much identical to class diagram

7. Which is Best? • Familiarity and personal preference • What standard you are told to use • Which standard the software we are using i.e. Enterprise Architect “encourages” us to use • For this module we will be using the Crows Foot notation • Supported by Enterprise Architect allowing auto generated code

8. What is the Difference between a Class and an Entity? • It really doesn’t help much when we have two documentation standards that look so similar • The rectangular boxes are inevitably going to look pretty similar until the subtleties are fully appreciated

9. So what are the differences? • Classes do not persist • Classes are ultimately implemented as objects • When the function or program ends, the object ceases to exist • Entities are ultimately implemented as tables • Tables do not vanish once the program/function has completed

10. Classes have Functionality Entities do not clsAddressBook MyAddressBook = new clsAddressBook(); MyAddressBook.FilterByPostCode(“LE1”);

11. “But really – aren’t they basically the same thing?” • Compare the following… • Entity framework automates much of this

12. Customer Name Delivery Address Post Code Widget Order Order line 1 Product A Unit Cost Quant Tot Order line 1 Product B Unit Cost Quant Tot • OrderTotal Objects are not based on Entities!

13. More Complex Relationship • The data for the class would be derived from multiple tables • For example • Customer details would come from tblCustomer • Product details would come from tblProduct • There may be a stock check facility making use of tblStock • Some elements of the invoice would never ever be stored in the system • For example • Order line totals and the invoice total would be calculated on the fly • When the invoice is stored in the system the data contained in a single object would be distributed across multiple tables.

14. Normalisation/De-Normalisation Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy. Normalization usually involves dividing large tables into smaller (and less redundant) tables and defining relationships between them. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database using the defined relationships. (Wikipedea)

15. Things to Consider • No row order significance

16. No Column Order Significance

17. Each attribute value must have only a single value

18. No Duplication

19. Order Line Order Order Product Product Always Decompose Many to Many Relationships

20. A B One to One Relationships • Do not exist (probably).

21. De-Normalisation • Some times we deliberately step back from a normalised database to meet other requirements • E.g. System response time

22. Other Places to Inform our Entity Design • Smoke and mirrors prototype • Class diagram • Sequence diagram