If you’re tasked with helping design a new database for your organization, it’s important that you create a good specification. If you’re working with an outside developer, the specification you’ve created will be the blueprint for the end product.
This post is the first in a series on how to design a specification.
Step #1: Think about your data entities
What is a “data entity”?
It’s the type of person or thing that your database tracks. For example, if you work for a hospital then the data entity for your database is probably patients. If you work for a non-profit organization, the data entity may be members.
Now ask yourself this: Is the data entity a person, organization, or thing? Here are examples of each type:
PERSON
• Members
• Clients
• Staff
• Patients
• Students
• Customers
• Donors
ORGANIZATION
• Corporate Contributors
• Restaurants
• Clubhouses
• Youth Organizations
• Homeless Shelters
THING
• Books
• Videos
• Equipment
Most databases will have a primary data entity. This is the one data entity that the database is geared to track first and foremost. A hospital database tracks patients, a donations database tracks donors, a contact database tracks contacts, and a grant management system tracks grants.
However, databases will often track other data entities as well. For example, the hospital database is meant to store information about patients, but it probably also stores some information about doctors. The donations database tracks donors, but it certainly also tracks their donations (donations are a different data entity than donors), and it may also need to store information about events. A school database may have many data entities: students, teachers, classes, enrollment.
Determine the data entities you wish your database to track. Then think about the relationships these data entities have with each other.
There are 3 types of relationships:
o One-to-one
o One-to-many
o Many-to-many
Donors and donations have a one-to-many relationship. One donor can give many donations.
Donors and Board-of-Directors (BOD) have a one-to-one relationship. One donor can hold a single position on the BOD.
Products and orders have a many-to-many relationship. One product may appear on several orders, and one order may contain several products.
Many databases will need to store information about both people and their organizations. If this is the case with your database, then you need to answer some important questions:
Which is the primary data entity of your database, people or organizations?
In many cases the primary data entity is people, but for each person in the database a field called “Organization” is used to store the name of their employer.
If it turns out that the primary data entity for your database is organizations, then the next thing you need to decide is what kind of contact information you need to keep about people within each organization. Does each organization have a single contact person, or do you need to store contact information for many employees within each organization?
Once you’ve answered these question, you’re ready to move on to step #2.