picture by Travis via Flickr
In the database literature, the lore has been to first design a schema and then predict the query mix to decide on a tuning strategy. This approach can work, but it is risky and error prone. A small misjudgment of the query mix can lead to vastly inferior performance. For many applications a better strategy is to index every foreign key. That is often sufficient and further tuning is unnecessary.
Continue reading Please Index Foreign Keys
picture by Thor via Flickr
Here’s a chapter from my book Patterns of Data Modeling. This chapter presents six data modeling patterns for trees — UML data models, IE data models, SQL queries, and examples. The book has much more information about patterns including patterns for directed graphs, patterns for undirected graphs, antipatterns, and archetypes.
picture by Theen Moy via Flickr
Many organizations today outsource software development. You, the customer, specify your requirements and the vendor is supposed to build software that meets your requirements. But requirements are not sufficient as a specification. With this common scenario, you have no control over the quality of the software being built. Furthermore, you have no visibility into the software’s structure. You can improve this situation by adding a data model and a database design to your specifications.
Continue reading Specify the Data Model and the Database Design
It’s been 25 years since these GE Research folks published “Object-Oriented Modelling and Design.” From left to right, Bill Lorensen, Jim Rumbaugh, Mike Blaha, Bill Premerlani and Fred Eddy. We had no idea at the time that the book would sell over 200,000 copies.
Here is an example of an IE data model. This is from my Infinite Skills video course Learning Data Modeling. The model was created with ERwin. This corresponds to the UML data model.
Here is an example of a UML (Unified Modeling Language) data model. This is from my Infinite Skills video course Learning Data Modeling. The model was created with the Enterprise Architect tool by Sparx Systems. This corresponds to the IE data model.
Mike Blaha will be at Data Modeling Zone (October 5-7, 2015 in Chapel Hill, NC) and will be presenting twice at the conference.
- Monday October 5, 12:00-1:00 PM — Data Modeler 2020 – Future of Data Modeling Panel
- Tuesday October 6, 2:15-3:15 PM — Advanced SQL Queries
Save 20% by using the code “BLAHA” in your registration for Data Modeling Zone. Send us an email (email@example.com) if you do and we’ll give you one of Michael Blaha’s books.
picture by Rob Blank via Flickr
That’s what we often tell our clients. If you build a database properly, performance will be fast for 80% of applications. The remaining 20% are outside the scope of this article – they have complex issues such as large schema, massive data, multi-user contention, and intricate logic.
Here are ten guidelines that developers can use to obtain good database performance. The guidelines apply to the day-to-day operational systems that run on a relational database.
Continue reading Don’t Worry About Database Performance — Ten Guidelines
picture by nerovlvo via Flickr
Relational databases are often advertised as easy to use – that’s true for a single table. However a database of hundreds or thousands of interconnected tables is complex and difficult to use. It is not helpful for marketers to set such misleading expectations – all it does is lead to frustration when projects overrun on cost and time and sometimes fail.
Continue reading Relational Databases are Not Easy to Use; They are Easy to Abuse
picture by Jessica Fenlon via Flickr
A use case is a piece of functionality that an app can perform. Each app has many use cases and the use cases taken collectively specify the app’s functionality. For an example, consider an app for tracking library loan records. Some use cases are: borrow books, borrow magazines, return books, return magazines, renew books, renew magazines, pay fines, get library card, and change address.
Continue reading Use Cases Are Overblown