Standard Data Types for Web Addresses, Phone Numbers?

[WWGD]

Hi All,
Just curious: what kind of data types does one usually use for web addresses, for phone numbers?
EDIT: I am using MSSQL 2014 .
Thanks.

 

[BvU]

No standards in this jungle. E.g. for phone numbers the forms (or whatever) designers usually take their local favorite and thus exclude the rest of the world. Same with addresses, date/time, etc. etc.

Side note: I had fun long ago discovering that the default data type for windows forms text boxes turned out to be currency !

As Georgie says: What else ?

 

[Borg]

Hi All,
Just curious: what kind of data types does one usually use for web addresses, for phone numbers?
Thanks.

If you're referring to how to store them in a database, it would probably have to be as some form of a string like varchar.

 

[Mark44]

Hi All,
Just curious: what kind of data types does one usually use for web addresses, for phone numbers?
Thanks.

Strings?

Phone numbers are really strings of digits. US phone numbers are 10 digits (not including the 1 prefix). International phone numbers are 12 digits, including the two-digit country prefix, but not including the + prefix that you sometimes see.

By "web address" do you mean the URL of a web page, and not an e-mail address?

 

[WWGD]

If you're referring to how to store them in a database, it would probably have to be as some form of a string like varchar.

Thanks; is it possible to store the webpage as a link in MSSQL 2014, i.e., so that one can double-click in the DB and access the page?

 

[WWGD]

Strings?

Phone numbers are really strings of digits. US phone numbers are 10 digits (not including the 1 prefix). International phone numbers are 12 digits, including the two-digit country prefix, but not including the + prefix that you sometimes see.

By "web address" do you mean the URL of a web page, and not an e-mail address?

Now that you mention it, I mean both.

 

[Borg]

Thanks; is it possible to store the webpage as a link in MSSQL 2014, i.e., so that one can double-click in the DB and access the page?

I haven't worked with MSSQL. God only knows what they allow.

 

[WWGD]

I haven't worked with MSSQL. God only knows what they allow.

Are you insinuating ( the very obvious statement that) Microsoft has a sloppy technical design?

 

[Mark44]

Now that you mention it, I mean both.

For web pages, I'm not aware of any particular format. For an e-mail address there is the user portion (the part before @) and the domain name (after @), which can include a subdomain. This is something I used to work with, but it's been awhile.

 

[newjerseyrunner]

No standards in this jungle. E.g. for phone numbers the forms (or whatever) designers usually take their local favorite and thus exclude the rest of the world. Same with addresses, date/time, etc. etc.

There absolutely are standards, I was an engineer for multiple telecom systems sold all over the world. For web addresses, refer to RFC7230 and use varchar(8000) and for phone numbers refer to RFC3966 and use varchar(22);

 

[QuantumQuest]

I usually work with PHP / MySQL and although I prefer to do as many things as I can inside the database - create stored procedures, triggers etc. wherever I have the chance to do it, most things are usually done through server side scripting. As mentioned above, varchar is the preferred data type for phone numbers, for they may contain symbols as well. For web addresses, you can store them as varchar too and create the links "on the fly" - as we usually say, through scripting.

 

[Silicon Waffle]

Hi All,
Just curious: what kind of data types does one usually use for web addresses, for phone numbers?
EDIT: I am using MSSQL 2014 .
Thanks.

It depends.
If your web addresses are something like google.com, physicsforums.com etc then it would definitely be varchar(Max value defined in RFC)
In case they are something like 12.34.56.78 and you need to store a millions to billions of them, then a single separate table with tinyint types may be preferred. A script or some methods need to be used then to construct the requested address.
For the phone numbers, it is almost the same, you might probably need to head into int types but some numbers like 1800-CALL-ME will surely ask you to make use of a bit of extra memory to store the strings under consideration.

 

[256bits]

but some numbers like 1800-CALL-ME

Such numbers are not unique - how would one handle conflicts and testing.

 

[newjerseyrunner]

Such numbers are not unique - how would one handle conflicts and testing.

Normalize it before putting it in the database and doing and queries on it, make that your primary key. Add another field for the way the user provided the number.

 

[WWGD]

Normalize it before putting it in the database and doing and queries on it, make that your primary key. Add another field for the way the user provided the number.

Agree, of course there should be a format, a single standard used, same as with dates and all other fields.

 

[pbuk]

Thanks; is it possible to store the webpage as a link in MSSQL 2014, i.e., so that one can double-click in the DB and access the page?

I think you are confusing the database engine with whatever GUI front end tool you are using.

 

[WWGD]

I think you are confusing the database engine with whatever GUI front end tool you are using.

Thanks; I thought there was a default choice of GUI; not sure the name, but I thought there was one.

 

[WWGD]

Normalize it before putting it in the database and doing and queries on it, make that your primary key. Add another field for the way the user provided the number.

Sorry, I quoted your post but somehow replied to a different one. Senility happening earlier than I expected :(.

 

[WWGD]

Sorry to bother you all again, how about sthe data type for street addresses, is this also a varchar(n)? Addresses always gave me problems in terms of normalizing; not clear where they begin, where they end, unless maybe if there is a general format, e.g., street number street name, etc.

 

[newjerseyrunner]

I would usually break it up before putting it in, provide different text boxes to ask the user for each part of the address, usually:
AddressLine1 (1600 Pennsylvania Ave)
AddressLine2 (Suite 1B)
City (Washington)
State/Province (DC)
Zip/Postal (20500)
Country (United States)
AddressType (Home)

Trying to take it in all at once and parse it is possible, but it'd be difficult, especially if you have to dealt with international addresses.

I would make each of the a varchar except for AddressType, for which I would create an enumeration.

 

[WWGD]

I would usually break it up before putting it in, provide different text boxes to ask the user for each part of the address, usually:
AddressLine1 (1600 Pennsylvania Ave)
AddressLine2 (Suite 1B)
City (Washington)
State/Province (DC)
Zip/Postal (20500)
Country (United States)
AddressType (Home)

Trying to take it in all at once and parse it is possible, but it'd be difficult, especially if you have to dealt with international addresses.

I would make each of the a varchar except for AddressType, for which I would create an enumeration.

Thanks; wouldn't international addresses have the nchar() type?

 

[newjerseyrunner]

Thanks; wouldn't international addresses have the nchar() type?

Good catch, but we're both wrong. Use nvarchar.

varchar doesn't support unicode, so you don't want to use that for international.
nchar however, is fixed length and will cause your database to grow quickly.

nvarchar both grows dynamically and supports unicode.

 

[QuantumQuest]

It is generally a good idea to break the address into parts for input, as newjerseyrunner said and depending on your database design, it is a good idea to have the addresses in a separate table with foreign key e.g. the surname - or what helps more, as you may have two or more addresses per person and have separate columns for each part of the address. As for the data type, usually varchar is convenient, because you may have numbers with letters and/or symbols and you don't need anyway to carry any arithmetic operations on them.

 

[QuantumQuest]

For international addresses, use nvarchar rather than varchar, in order to avoid coding conversions for each read / write on the database.

EDIT: Here of course I'm talking specifically about MSSQL Server.

 

[pbuk]

Thanks; I thought there was a default choice of GUI; not sure the name, but I thought there was one.

99% of people using a MS SQL Server database access it via a bespoke application and 99% of the remainder through ODBC. SQL Server Management Studio is not designed as an end-user tool.

It is generally a good idea to break the address into parts for input, as newjerseyrunner said and depending on your database design, it is a good idea to have the addresses in a separate table with foreign key e.g. the surname - or what helps more, as you may have two or more addresses per person and have separate columns for each part of the address. As for the data type, usually varchar is convenient, because you may have numbers with letters and/or symbols and you don't need anyway to carry any arithmetic operations on them.

Usually best to have the address table keyed on an IDENTITY column and use that as a foreign key in the People table. It is generally a bad idea to use a data column as a foreign "key" because it is difficult to ensure (i) uniqueness (what happens when two people have the same surname?) and (ii) immutability (what happens when someone changes their surname)?

I have never regretted the decision to use an IDENTITY column as a primary key for every table (except usually the joining table in many-to-many relationships). I use a common name (Id) but some people include the table name or a singular version of it (e.g. People.PersonId).

CREATE TABLE Addresses
(
   Id int NOT NULL,
   CONSTRAINT PK_Addresses_Id PRIMARY KEY CLUSTERED (Id)
);

(although CLUSTERED is the default for a PRIMARY KEY I specify it out of habit so that it throws an error if another clustered key exists: for performance reasons you want your primary key to be the unique clustered index for a table).

 

[QuantumQuest]

Usually best to have the address table keyed on an IDENTITY column and use that as a foreign key in the People table. It is generally a bad idea to use a data column as a foreign "key" because it is difficult to ensure (i) uniqueness (what happens when two people have the same surname?) and (ii) immutability (what happens when someone changes their surname)?

I just gave an example of something very usually done. Of course you can choose another way to design your database, but I don't really understand what you mean by "uniqueness" on the foreign key. There is no such need for foreign keys. And also I can't see where do you find any immutability issue.

it is a good idea to have the addresses in a separate table with foreign key e.g. the surname - or what helps more

If "surname" does not help then another column can be chosen accordingly to the specific needs.

 

[pbuk]

I just gave an example of something very usually done.

And in my experience very often corrected years later at vast expense.

I don't really understand what you mean by "uniqueness" on the foreign key. There is no such need for foreign keys.

What happens when there are two people with the surname "Smith"?

And also I can't see where do you find any immutability issue.

What happens when Mary Smith gets married and changes her name to Mary Jones?

If "surname" does not help then another column can be chosen accordingly to the specific needs.

What happens when the specific needs change?

If you use an IDENTITY column (or the equivalent in other SQL dialects) the answer to all three of these questions is "nothing, it all still works".

 

[pbuk]

Just realized I missed the vital keyword out of this code:

CREATE TABLE Addresses
(
   Id int IDENTITY NOT NULL,
   CONSTRAINT PK_Addresses_Id PRIMARY KEY CLUSTERED (Id)
);

(although CLUSTERED is the default for a PRIMARY KEY I specify it out of habit so that it throws an error if another clustered key exists: for performance reasons you want your primary key to be the unique clustered index for a table).

 

[QuantumQuest]

@MrAnchovy: I don't want to go further on this discussion, because it is off topic. But just because you obviously do not understand what I wrote

And in my experience very often corrected years later at vast expense.

I don't know about your experience, but every professional who deals with databases, simply knows that any design depends on the problem at hand with its very details and there are various ways to think of it. There is no magic recipe that fits everything.

What happens when there are two people with the surname "Smith"?

You say the same thing that I answered before. I see a clear misunderstanding of foreign keys.

What happens when Mary Smith gets married and changes her name to Mary Jones?

There are various routines and database constructs to handle this through a foreign key. Trivially known.

What happens when the specific needs change?

I did not mean that you change anything according to needs, but choose it properly in the first place. But to answer this also, I use to create flexible designs.

As a final comment, if you believe that you have found a magic recipe then it's ok for me. But what I described for OP to use, are all tested through years and work fine.