Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I'm currently evaluating a potential new project that involves creating a DB for typical customer information (userid, pwd, first & last name, email, adress, telfnr ...). At this point, requirements are only roughly defined.

The customer DB is expected in the O(millions) of records. In order to calculate some back-of-the-envelope numbers for DB sizing and evaluate potential DB options & architectures, I'm looking for some de-facto standards for these kind of records. In particular, the std size of every field (first name, last name, address,...) or typical avg for a simple customer record would be great info.

With so many e-commerce websites out there, there should be some kind of typical config that can be reused and avoid re-inventing the wheel.

Any ideas?

---- edit ----

The answers seems to be steering towards adopting an standard customer record vs designing your own. I would like to stress that the focus of this question is to locate a reference for field sizing for a customer object, and avoid figuring that out on my own.(I've emphasized that part on the original text -now in bold-)

share|improve this question

closed as too broad by gnat, Kilian Foth, MichaelT, GlenH7, Martijn Pieters Feb 17 at 22:13

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

1  
I would like to see info on this too. I have never found anything like this either though. What would be interesting is if someone did a case study on this by viewing some open source projects. –  Jason Holland Jun 23 '12 at 15:44
2  
pity you got downvotes for what is really a good question about software 'professionalism' by not reinventing your own record format. –  gbjbaanb Jun 23 '12 at 15:53
    
Man, I wish there was any sort of consistency in this area. I've imported customer databases from a good number of systems, and they're all over the map. –  TehShrike Jun 23 '12 at 17:37
    
are you planning to store information about phone number, address etc for only one particular country? It can make a differnce in the size you need. –  HLGEM Jul 9 '12 at 21:29
1  
Might want to check out Association for Retail Technology Standards Data Model Home Page –  User Jul 10 '12 at 2:04
show 5 more comments

7 Answers

The nice thing about standards is that you have so many to choose from. - Andrew Stuart Tanenbaum

Things like this are very specific to a customer and the industry, anything generic will include everything and the kitchen sink. Especially EDI type formats, they were organically defined over a decade or more in most cases and include everything every company on the committee ever wanted. They were supposed to be industry generic, and they became extremely industry specific and extremely brittle.

There is no royal road to the design or information you want. Do the time and the effort to get the requirements and get a concrete estimate. Otherwise you will be more wrong than correct. The only way to know what you need to know is to ask the questions and figure it out yourself.

Many CRM systems use what is now called an Expando object pattern, previously known as a dynamic property pattern. It is basically a key value pair dictionary construct. Except for very special cases it is considered an design Anti-Pattern and should be avoided.

I have designed and built at least 8 custom CRM solutions in the last 20 years, each and everyone had different requirements and none of the data models ( logical or physical ) would have worked across the board for all the domains.

Specific solutions for specific cases will always be better designs.

share|improve this answer
    
I wish I could +2 for the last sentence! –  Maxpm Jun 24 '12 at 23:00
    
Thanks. I agree with most of your points and I certainly would base a design on a proper requirement phase. That said, for back-of-the-envelop kind of calculations I'd certainly appreciate sensible defaults that could be taken from a reasonable 'de-facto' standard, so not a standard like the EDI formats, but rather something people are using in some widespread fashion. That way I could assemble my customer object and get an ball-park figure on record size. –  maasg Jul 9 '12 at 14:10
    
My point is missed, anything used in widespread fashion is going to be much too broad and generalized to be useful. It will be bloated and overly complicated. This is where SAP, PeopleSoft and Salesforce make their money. Their stuff is generalized to such and extent you have to pay high $$$ consultants to customized it to fit your companies needs. Usually this cost many times what a straight up custom solution would cost to develop and maintain. And they make their money on the on going after work, with constant incompatible upgrades and the like. –  Jarrod Roberson Jul 14 '12 at 4:50
add comment

There is a thread in the DBA stack on best practices for common person fields that discusses the issues. It matters a great deal what you are planning to do with the data and how thorough you need to be. If you actually need to support all valid email addresses or all valid names, your columns are going to need to be much bigger than if you merely want to support whatever your organization and application consider a reasonable subset of the valid values.

share|improve this answer
    
That's the closest I've come to an answer to my question. Those guidelines are quite good although not complete or concrete, but definitively in the right spirit. –  maasg Jul 16 '12 at 11:01
add comment

As Jarrod pointed out, if you follow a generic standard, you will definitely end up with a record format that includes a lot of things your system will never need. Since you already know that there will be a fairly large number of records, it's likely that you'll get unnecessary performance issues because you're supporting data that will never be used. Conversely, it's also likely that the standard won't include fields that you do need, which will be a painful problem to solve; either you break the standard by adding these fields, or you'll have to find some (probably clunky) way of including the non-standard fields within the standard.

I think the real problem here isn't about finding a one-size-fits-all standard (which will almost always be one-size-fits-NONE) but that you have been tasked to estimate a solution where the requirements aren't specified yet. In these cases, I think the only professional thing to do is to make a minimum estimate based on the requirements you do have, and then make a maximum estimate based on all possible undefined requirements that you think might come up. Sure enough, the estimate might become ridiculously rough, in which case you should explain to whoever tasked you with this, that it's just not feasible to make a good estimate until the requirements are more well-defined.

share|improve this answer
add comment

I'd say you need to find standards for EDI systems. There are hundreds of 'standard' documents, so you'd need to choose one based on your requirements. For example, here's a format for TRADACOMS invoice that you could grab fields you want from.

share|improve this answer
add comment

The Open Applications Group publishes a set of open standards for application implementation and interoperability. They're mostly XML-oriented, but they do specify a standard customer record with individual fields and sizes (look for CustomerPartyMaster in the document standards list).

share|improve this answer
add comment

Existing International Standards

There are quite a few standards, but specific to certain fields, with varying requirements for each of them depending on their data collection needs.

For instance, but not limited to (and talking from experience with both of these):

Some of the above link to fairly detailed documents, listing even requirements for health and formatting of fields (for instance, HL7 uses well-defined data-types). At lot of them do not go in this much detail though.

Government-Driven Standards for Internal Records

Governments, national or local, often have a strong need to record and store personal information for public offices, and obviously have come up with own "standards", which they implement across their organizations (with varying levels of success and interoperability with partner organizations).

An example could be this Data Formats for Identity Records Standard from the Government of New Zealand.

De-Facto Standards in Software

You could take inspiration from these, or use the source of known open-source CRM software to use as best-practices and guidelines for the data specifications of your customer data.

See the Top 10 Open-Source Business and Social CRM Software list, for which you could look up their data-models yourself.

share|improve this answer
    
De-Facto Standards in Software -> very interested in these. Could you add some references? –  maasg Jul 10 '12 at 12:04
    
Downvoters, please explain (there were 2 just now). –  haylem Jul 14 '12 at 10:20
add comment

I would say "You ain't gonna need it (yet)". And with Ron Jeffries: "Always implement things when you actually need them, never when you just foresee that you need them."

So maybe if it is time to add a concrete database to the project you have much more knowledge about the data that will be stored there.

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.