Restructuring the pdNickname database

An alternative structure for is to have one record per name with the variations in fields next to it. This tutorial explains how to do it.

Matching and merging names can be tricky. How do you relate William Smith with Bill Smith? The pdNickname database can be utilized to match names that are dissimilar because one has a given first name while another has a nickname or other variation.

Out of the box pdNickname is structured to allow immediate compatibility with the greatest number of database systems as well as to make it easy to become familiar with.

The nickname database is setup with two names per record. The first name field contains the names you are looking up, and in the second is a variation for each name—nickname, diminutive, given name, variant, etc. The same name can be listed several times in the first field, each time with a different variation. (See Figure 1.)

FIGURE 1: PDNICKNAME OUT OF THE BOX

If the names compared are Alexander Jones and Alex Jones, all names matching Alexander (NAME-A) are scanned until a variation is found that matches Alex (NAME-B). This works well, but there are other ways of organizing pdNickname that could work even better for you. In fact, we have restructured the table for utilization in our own services.

An alternative structure is to have one record per name and the variations in fields next to it. It is not practical to have separate fields for each variation, which can range from one to over two hundred. So what we do is have two Memo fields (also known as Long Text), one for close variations (relflag = "1") and the other for more distant variations (relflag = "2"), with the string of variations separated by delimiters for easier matching. (See Figure 2.)

FIGURE 2: PDNICKNAME RESTRUCTURED

Note: when browsing a table, normally you cannot see the content of a Memo or Long Text field because the database keeps it in a separate file. For this screenshot we have made the content visible.

Structured this way, when your program finds a match for NAME-A, it then determines if NAME-B can be found in variation field one or variation field two. This can be faster because you only access one record in each search request.

pdNickname, like all our Database Products, are structured to satisfy most users from the start. But there are many ways to integrate the databases into your system. It is up to you to determine what works best for you. Do not be afraid to experiment.

Start at the finish line

When planning a new data management projects, results will be better, costs lower and headaches lessened if you consider what your objectives are before you begin. Always start by thinking about what you want to do with your data both now and down the road.

The worst kind of database system is one put together piecemeal as new demands arise. At some point it becomes more of a hindrance that a help. Many become monsters that seem to have a life of their own.

Before you design your databases, tables and user interfaces and decide on purchases, consider all the kinds of data you want to track and the best and most resourceful way of doing so. But to do this you need to gather some information first.

Talk to the end users who will utilize your data. Find out what they need and how they will be using it. Just as important, determine what they would like to do in the future and what has frustrated them most about data in the past.

Also talk to those who will be entering information and the techs who will be working directly with the database system. Find out what will make them more efficient and what has previously held them back.

Finally talk to the vendors who will be processing your data and supplying equipment and third-party lists. Ask them what you can do to help them achieve the best results for you and reduce costs without sacrificing quality.

All of this will affect what tables you design, what fields they will contain, what relationships there will be between them and how end-users will access the information. It will also affect what equipment and lists you acquire, when you buy them and who you hire to make it all work.

Your new data management project should not be planned until after gathering the insight needed to establish what the end results should be. Once you have seen the view from the finish line, you will be much better equipped to create a database system that will get you where you want to be days, months and years from now.

Hello world!

Welcome aboard! We are happy you could join us. This is the blog for Peacock Data. Everyone involved with this project sincerely hopes you enjoy it and find it useful. Please visit us often and feel free to contribute. You can also follow us via Twitter, Facebook, and RSS Feed.

About Blog@PeacockData

This blog is designed to communicate about all aspects of database management. Here you will read about related topics and issues, learn from guest bloggers, and explore new and time-honored solutions as well as collect tips and advice. Of course you will also read about us, including important information about new and enhanced services and products, sales and discounts, personnel changes, and what else is going on with our company.

And because this is a blog, you can participate too by leaving comments and asking questions, and we encourage this highly. There is a reply box at the bottom of individual post pages—please utilize this feature.

It is astonishing how many at any given moment are in the same boat as us, experiencing the same problems and concerns, but we do not know they are there. Blog@PeacockData not only gives you the opportunity to gain valuable knowledge but also the ability to communicate with those in the boat. We fully expect you will not only benefit from and help others in similar situations but will help us improve as well. In the end all boats will rise.

About us

We are about quality. Quality database products, quality customer support, and most importantly, quality results for those utilizing our products. They are designed to make database systems more useful, more accurate, and easier to use.

With more than twenty years experience, Peacock Data is an industry leader because of our superior solutions and our renowned loyalty to customers. We are committed to total client satisfaction, state-of-the-art technology, innovation, and fair pricing. For us it’s the service AFTER the sale that counts!

Again, welcome aboard the good ship Blog@PeacockData. We genuinely believe you will find this site an exceptional resource—not just another blog.