FREE DM Review Site Registration!
Sign-up today and access DM Review on the Web!

Your FREE registration entitles you to:
FREE email newsletters

FREE access to all DM Review content

FREE access to web seminars, resource portals, our white paper library and more!

   

We are going to change the numbering format for our customers for database across the organization. What should we consider before beginning the project?

Question: We are going to change the numbering format for our customers for DB across the organization. Now we are using four digits
CR NN
C - Country
R - Region
N - current customer number. CC RR NN.
Could you please advise what we need to consider before starting the project?

Adrienne Tannenbaum's Answer: Intelligent identifiers (what you have here) always get you into trouble. If in fact, customers are currently identified as a combined country, region, customer number grouping, what do you do when a customer leaves a particular region to relocate? If you need to keep the same customer number, what if there is already a customer in the new country/region that has that customer number? What do you do when your regions get redefined? I could go on and on but you are asking for trouble if you have not experienced it yet.

Without knowing why you want to expand this number to begin with (too many customers in one or more regions, new regions being defined that split up old ones, etc.), I can suggest a total revamp of the way you identify your customers.

The best way to handle this is to separate country and region from your customer ID. Keep them as separate attributes, probably foreign keys to country and region tables. The expansion in your identifier should go purely to the nonintelligent customer number - make it go from two to eight.

Evan Levy's Answer: I'd encourage you to rethink your use of an intelligent key. I'm not fond of embedding "intelligence" or "logic" when developing a coding scheme. The reason is simple; there's always a limit to extensibility. As you've already learned, your old number scheme was limited to 10 country IDs (assuming you used digits 0-9), 10 regions (digits 0-9) and 100 customer numbers (digits 00-99). This way of doing things limited you to three different attributes to describe your customers; there was also a limit of 10,000 values (although, you probably had a much smaller limit because you don't likely have 99 customers in each region and 10 regions in each country).

I assume that your current system requires some sort of lookup table in order for the users to understand the meaning of the individual values.

If you instead used an "unintelligent" key where the value was simply a number that incremented when a new customer was identified, you could associate a much larger quantity of attributes with a much higher degree of flexibility. Of course, this would still require a lookup table to determine the attributes and values associated with a specific customer ID; however, it would be dramatically more flexible.

Larissa Moss' Answer: All primary keys should be nonintelligent keys. In other words, they should be "dumb numbers" with no meaning to them. The only purpose for a "Customer Number" primary key is to uniquely identify each customer irrespective of country or region or any other related attribute. CR NN or CC RR NN or any other similar scheme is a composite key comprised of multiple intelligent numbers because the C and R stand for a specific country and region. These two attributes (country code and region code) should not be part of a customer primary key. Country code and Region code are primary keys in themselves for countries and regions respectively. They each may relate to customers (either as foreign keys or be denormalized into the customer entity) but they should not be fused with an independent customer number - for obvious reasons: What if the same customer moved from one region to another or from one country to another? With your numbering schemes you would have to assign that customer a new number reflecting their new geographic location, which would impair your ability to track the customer over time and over geographies.

Chuck Kelley's Answer: I would consider how many of each there could possibly be. For example, I know that there are more than 10 countries, however you may never do business in more than 10, so that would be OK. I am not sure what you mean by region. If I use the U.S. as a Country, then it could be possible to have 50+ regions (depending on your definition). I would probably increase that to three to four digits. As for current customer number, I would think that you would like to increase this, but if the value is based on customer number within Country and Region, that might be OK. If your business community believes that CCRRNN is the best, then I would consider going with that.

Clay Rehm's Answer: Here are some thoughts to consider. Building 'intelligence" into the number can cause issues. This means that people can recognize some categorization when viewing the number. This works well for users, but when it needs to change, the conversion can become very difficult. If you use a sequential numbering scheme for example with no hidden meanings in the number and if it is made large enough, this can work well. If your users are convinced they need to keep the country and region in the number, make sure each component is big enough for any future growth of your company.


Larissa Moss is founder and president of Method Focus Inc., a company specializing in improving the quality of business information systems. She has more than 20 years of IT experience with information asset management. Moss is coauthor of three books: Data Warehouse Project Management (Addison-Wesley, 2000), Impossible Data Warehouse Situations (Addison-Wesley, 2002) and Business Intelligence Roadmap: The Complete Project Lifecycle for Decision- Support Applications (Addison-Wesley, 2003). Moss can be reached at methodfocus@earthlink.net.

Chuck Kelley is an internationally known expert in database and data warehousing technology. He has 30 years of experience in designing and implementing operational/production systems and data warehouses. Kelley has worked in some facet of the design and implementation phase of more than 50 data warehouses and data marts. He also teaches seminars, co-authored four books on data warehousing and has been published in many trade magazines on database technology, data warehousing and enterprise data strategies. He can be contacted at chuckkelley@usa.net.

Clay Rehm, CCP, PMP, is president of Rehm Technology (www.rehmtech.com), a consulting firm specializing in data integration solutions. Rehm provides hands-on expertise in project management, assessments, methodologies, data modeling, database design, metadata and systems analysis, design and development. He has worked in multiple platforms and his experience spans operational and data warehouse environments. Rehm is a technical book editor and is a co-author of the book, Impossible Data Warehouse Situations with Solutions from the Experts. In addition, he is a Certified Computing Professional (CCP), a certified Project Management Professional (PMP), holds a Bachelors of Science degree in Computer Science and a Masters Degree in Software Engineering from Carroll College. He can be reached at clay.rehm@rehmtech.com.

Adrienne Tannenbaum is president of Database Design Solutions, Inc. (www.dbdsolutions.com), a New Jersey-based consulting firm specializing in the revitalization of corporate data. The firm focuses on data issues within large organizations and supports all data reconstruction efforts with a solid meta data backbone. Tannenbaum is the author of two popular meta data-focused books: Metadata Solutions: Using Metamodels, Repositories, XML, and Enterprise Portals to Generate Information on Demand (2001, Addison Wesley) and Implementing a Corporate Repository (1994, Wiley).

Evan Levy is a partner and co-founder of Baseline Consulting Group, a multivendor systems integration and consulting firm. As the partner in charge of Baseline’s largest practice, Levy leads both executives and practitioners in delivering technology solutions that help business users make better decisions. He has led strategic technology implementations at commercial and public sector organizations and advises vendors on their product development and delivery strategies. Levy has been published in a wide array of industry magazines and has lectured on a range of technology delivery experiences at leading conferences and vendor events. He has been a featured speaker at the Marcus Evans Analytical CRM symposium, DCI’s Data Warehousing conference, the CRM Association, DAMA International, the AMA and the Data Warehousing Institute. His current work involves delivering and lecturing extensively on the topic of data integration. You can contact him at evanlevy@baseline-consulting.com.

For more information on related topics, visit the following channels:



Industry Vendors