I like to think about database design in terms of shapes. The shapes tend to be basic circles, triangles, squares, etc. things that are easily understood when explained. So, simple relationships between data can be explained and conceptualized using the same things object developers use. I like the bubble diagrams to start.
Let’s say you have are charged with putting together a simple contact manager. There are many on the market, but yours needs some special features and your company doesn’t want to spend the money to customize another application. So, you decide to build one.
What basic features needs to be built in? Customer records, contact records, and a flow to manage them all stem from a good database design and understanding of the data. Here are the top level parts:
- Contact records
- Action Items
- Follow up Alerts
The Customer record will encompass the majority of the design, because there are many parts to that one entity. I’ll use the word “entity” a lot to represent one “thing”, one “noun” or what could be considered master data. We’ll talk about master data in another blog post, but for now know that master data is a business critical noun. OK, back on topic. The Customer record may contain some other entities, or sub-entities, address, phone, contact name, company name, etc.
The Customer record then starts to take shape, but there are some more things to consider. Are there more addresses to each customer? How many contacts, people, are important and how many phones do they have? The data really starts to become a hierarchy:
- Phone Numbers
- Contact Names
All of these live inside the bubble of the Customer, even if these elements are referenced by contact record, the record of a customer contact. We will get into that later, after we blow out the rest of the customer record.