Clever Geek Handbook

SQL 2005 Standard Data Types

I am putting together a database of locations to find the closest places for a given address. When I started laying out my business spreadsheet, I wondered what other people were doing and if there were “best practices” for some common data types. Such as phone numbers, addresses, and even latitude and longitude.

This is what I have so far. Any suggestions or improvements?

These must all be US addresses, FYI.

 Column          DataType
 ------        ------------
 id            int
 city          nvarchar(100) 
 address       nvarchar(100) 
 address2      nvarchar(100)
 state         varchar(2)
 zip           nvarchar(10)
 phone         nvarchar(14)
 fax           nvarchar(14) 
 name          nvarchar(100)
 latitude      float
 longitude     float
 notes         text
+2


source to share


8 answers


Considering you only need US addresses, I would go with varchar for fax, phone, and zip code. Perhaps you can even use varchar for City, Address and AddressExtra.

If you are going to print the address, you may need some way to break it up line by line, but you only have one column, which might be a little short.



I would normalize the notes to my table so you can keep track of them by date or type.

you may need status: "N" ew, "D", "X" deleted, "A" ctive, etc.

+1


source


I would use varchar (max) or nvarchar (max) instead of text ... I think TEXT is the "old way"



+3


source


Your phone number must support more characters. With today's VOIP phone network, some companies that are migrating from a regular phone line to a VOIP phone line sometimes get a new phone extension in large numbers.

For example, when I want to call my friend at work, I need to type something like

1-888-555-5555 x667833

It's 17 characters if you want to keep it stripped of all characters (18885555555667833). If you want to allow free text (for example, 1-888-555-5555 ask Bob), you should consider more characters.

+2


source


I found it helpful to use names borrowed from the hCard microformat . Some are for humans and some are for addresses, and this doesn't match 1: 1 with the hCard spec, however this subset has been pretty useful so far:

honorificPrefix    nVarChar(20)
givenName          nVarChar(50)
additionalName     nVarChar(50)
familyName         nVarChar(50)
nickName           nVarChar(50)
honorificSuffix    nVarChar(20)
fn                 nVarChar(50)  -- # Formatted name
email              nVarChar(255)
title              nVarChar(50)
role               nVarChar(50)
organizationName   nVarChar(100)
organizationUnit   nVarChar(100)
streetAddress      nVarChar(100)
extendedAddress1   nVarChar(100) -- # hCard specs support any number of these
extendedAddress2   nVarChar(100) -- # I've not seen a need for more than two.
locality           nVarChar(55)
region             nVarChar(2)
postalCode         nVarChar(20)
postOfficeBox      nVarChar(20)
countryName        nVarChar(50)
phone              nVarChar(25)
fax                nVarChar(25)
latitude           float
longitude          float

Experience has shown that these types of data and their sizes have worked well in many different directories of people and organizations in the United States. However, I do like some of the comments here that suggest a larger datatype for phone numbers. Also, I'm not sure if storing latitude and longitude as a float is the way to go, so your movement might change.

Good luck!

+2


source


I would not use float for lat long. I've seen some very bad rounding problems when this was done, and rounding in a long lat computation can get you in the wrong position. Use decimal instead and determine the number of slots you need.

+2


source


What industry will this database be used in?

Many industries have standards that can be used as guidelines for the design and implementation of objects, tables, and data types.

For example, when dealing with health and insurance data, one standard that influences my design is "ANSI ASC X12N 837 Health Claim Claims (837)".

If you ever plan to import or export information from external systems, I would use the standard standard as a guide.

0


source


I wouldn't dare to name these best practices, but here I found out that this is the hard way of advice:

  • General : Use nvarchar over varchar everywhere here. The missing columns are ok.
  • id . I prefer to use a unique identifier here. It may be necessary to preserve uniqueness across different tables. Use newsquentialid () and create a clustered index on it.
  • address . Customize three of them.
  • state . Go to more neutral territory and change it to nvarchar (100). Ideally, change this to propertyId (uniqueidentifier) ​​and get another table that contains them.
  • zip : change to postalCode and increase the size to 20.
  • telephone . Increase the size to 50. I find it best not to get involved in trying to break this check unless you really have a good reason and plan to use it.
  • fax : same as telephone
  • notes : change this to nvarchar (max) so you don't run into so many restrictions.
0


source


I never figured out how to "comment" instead of "reply"

But I am not following the warning about rounding problems with lat / long. The degrees, rounded to four decimals, are the maximum error of two meters. Are you lost if you are two meters away? If so, then no GPS device will ever help you.

Although if you are using google maps to convert address to lat / long you might be in a block.

0


source






More articles:


All Articles