Field Lengths Survey | Tech Off

Larsenal
ready to give an answer

There are a lot of common fields used in databases. When you create a table, what size do you set for each of the following fields:

Username
Password
FirstName
LastName
Company
Address
Address2
City
State/Province
ZIP/PostalCode
Email
Telephone/Fax
(Any other common ones I've forgot)

I'm very curious to see whether there is a consensus.

Matthew van Eerde
AKA Maurits

nvarchar(255)

Larsenal
ready to give an answer

Matthew van Eerde wrote:

nvarchar(255)

You use that for all of 'em? Seriously?

alexmac

Yeah I use varchar(255) as well. Unless performance and size are a real issue I found it to be safer (although slightly lazy!).

AdrianJMartin

why have address1 and address2?

why not just have address?

what if you need address3......

insane!

just have address and do your formatting properly.

AddressN fields come from old character based forms systems - move on!

Dr Herbie
Half the population have below average IQ

AdrianJMartin wrote:

why have address1 and address2?

why not just have address?

what if you need address3......

insane!

just have address and do your formatting properly.

AddressN fields come from old character based forms systems - move on!

We can't! Our systems came from old character-based forms systems and we inherited the data-schema. We're not allowed to re-factor the schema either, so we're stuck with Address1 - Address4 + Postcode.

Incidentally, we use varchar(70) for address fields. No-one knows why 70 was chosen Expressionless

Herbie

stevo_
Casablanca != Manchester

How old the schema will be before its finally reviewed?

Sven Groot
You can't have everything; after all, where would you put it?

I hate length-limited address fields. My address is very long, it almost never fits. Sad

figuerres
???

Dr Herbie wrote:

AdrianJMartin wrote: why have address1 and address2?

why not just have address?

what if you need address3......

insane!

just have address and do your formatting properly.

AddressN fields come from old character based forms systems - move on!

We can't! Our systems came from old character-based forms systems and we inherited the data-schema. We're not allowed to re-factor the schema either, so we're stuck with Address1 - Address4 + Postcode.

Incidentally, we use varchar(70) for address fields. No-one knows why 70 was chosen

Herbie

depending on who first wrote the system and with what hardware:

1) punched cards and mainframe/mini systems tended to use 70 to allow for 10 chars for seqence number + a space
like:

1234567890[your data is here]

2) DOS style stuff like dbase III / CLipper etc... used screen layouts based on 80 columns so prompts used to be limited also:

F Name: [______________]
L Name: [______________]
and so on based on a 24 line x 80 char screen
char 80 was often not used to avoid problems (some screens/ code would put a \n and messup layout)

I have seen both cases together also: COBOL apps and VT100 screens....
or IBM termials....

figuerres
???

Larsenal wrote:

There are a lot of common fields used in databases. When you create a table, what size do you set for each of the following fields:

Username
Password
FirstName
LastName
Company
Address
Address2
City
State/Province
ZIP/PostalCode
Email
Telephone/Fax
(Any other common ones I've forgot)

I'm very curious to see whether there is a consensus.

nvarchar(MAX)

most of the time...

if the app is 100% US I may make state and postal small.
but in general I use nvarchar(MAX) as it let's the users enter a long name or address and nvarchar is unicode so I should be able to take any unicode input.

figuerres
???

AdrianJMartin wrote:

why have address1 and address2?

why not just have address?

what if you need address3......

insane!

just have address and do your formatting properly.

AddressN fields come from old character based forms systems - move on!

address and address1 will work for 99% of all addresses if they are 80 char or larger...
and I have seen that used in just about every system I have seen...
so it's a kind of "standard layout" that folks know and expect to find.

depemnding on use

Firstname,lastname,companyname,address1,address2,city,state,postalcode

are the basics...
innthe UI you can togger busines name on/off and add "Attention: firstname lastname" as needed in mailing etc...

harumscarum
out of memory

I would never allow that many chars because it would ruin my UI. Users shouldn't need that many anyways.

AdrianJMartin

harumscarum wrote:

I would never allow that many chars because it would ruin my UI. Users shouldn't need that many anyways.

(apologies if the above quote was intended as sarcastic!)

Statements like this from developers makes me really angry - how can you blame the user for breaking your UI, just because they live in a town with a long name......

( after writing that I'm even more convinced harumscarum was being sarcastic)

m1keread
Bored of Captions

AdrianJMartin wrote:

why have address1 and address2?

why not just have address?

what if you need address3......

insane!

just have address and do your formatting properly.

AddressN fields come from old character based forms systems - move on!

Please Explain.

Do you mean enter the whole address on one line ?

If so, parsing out the details would be a nightmare, because you cannot parse on spaces because they are valid in a single line. Users may enter commas after each line (or may not).

I can understand they come from old systems, but surely the model works if you need to process the data, i.e. for display on screen or printing.

m1keread
Bored of Captions

Just thought, do you mean House Number and Postcode and retrieve the rest from a lookup service?

stevo_
Casablanca != Manchester

m1keread wrote:

AdrianJMartin wrote: why have address1 and address2?

why not just have address?

what if you need address3......

insane!

just have address and do your formatting properly.

AddressN fields come from old character based forms systems - move on!

Please Explain.

Do you mean enter the whole address on one line ?

If so, parsing out the details would be a nightmare, because you cannot parse on spaces because they are valid in a single line. Users may enter commas after each line (or may not).

I can understand they come from old systems, but surely the model works if you need to process the data, i.e. for display on screen or printing.

You can store more than one line in a varchar..

blowdart
Peek-a-boo

Matthew van Eerde wrote:

nvarchar(255)

You foolz! I always use the XML type.

*duck*

Dr Herbie
Half the population have below average IQ

m1keread wrote:

Just thought, do you mean House Number and Postcode and retrieve the rest from a lookup service?

This assumes that the lookup service is up-to-date (which they often aren't). The lookup service we use includes the names of the occupants, which is often wrong -- it still lists me at the address I left 2 years ago.
Additionally, in the UK you can alert the post-office that you wish to 'name' your house, so that addresses can include a house name or number (the post office will verify that your house name is valid according to their rules). Lookup services may only allow house numbers, while the actual customer might want their posh house name in the address.

We use the lookup service to pre-fill the data entry form, but you have to allow the user to modify the details in case they're out of date. This means that you have to store the address details anyway.

Herbie

SlackmasterK
I write my OWN blogging engines

Larsenal wrote:

Username         varchar(50)
Password         varchar(100)   -- my encryption algorithms make everything bigger...
FirstName      varchar(50) -- international support; bigger
LastName      varchar(50) -- international support; bigger
Company      varchar(100)
Address      varchar(70) -- seems to work
Address2      varchar(70) -- seems to work
City            varchar(50)
State/Province -- varchar(2)
ZIP/PostalCode -- int
Email      -- varchar(50)
Telephone/Fax -- varchar(10)

SlackmasterK
I write my OWN blogging engines

stevo_ wrote:

You can store more than one line in a varchar..

Which brings me to another rant - I wish Management Studio let me type and edit multi-line cells, like Excel. As a result, anytime I want to change something in the database manually, I have to open a project I made for that specific purpose and fark with the connectionstring...

... Then we get into "Can't update, this table has changed"... No it hasn't... but I digress. Here, take your thread back.

Matthew van Eerde
AKA Maurits

SlackmasterK wrote:

ZIP/PostalCode -- int

int (arguably) works for ZIP (if you can kludge the starts-with-zero behavior and the +4 stuff) but won't work at all for PostalCodes, which are letter-number-letter number-letter-number (H0H 0H0 for Santa Clause)

figuerres
???

Matthew van Eerde wrote:

SlackmasterK wrote:
ZIP/PostalCode -- int

int (arguably) works for ZIP (if you can kludge the starts-with-zero behavior and the +4 stuff) but won't work at all for PostalCodes, which are letter-number-letter number-letter-number (H0H 0H0 for Santa Clause)