Table design question

Question

Hi,

I am having a problem choosing the variable types for database table. Can someone please give me some general guidelines as to how to choose the types? The following are some of the questions I have --

1. What should an userid be? An INT seems small as the design should take large number of users into account. So if not INT what else? BIGINT? VARCHAR? Am I not thinking straight?

2. When should I choose varchar and text and tinytext, binary?

I searched online and did not find any helpful links. Can someone point me in the right direction? May be I need to read book.

• Pav

Answer 1

I would actually make a user ID a character type since I'd rather log in as "pax" than 14860. However, if it has to be numeric (such as if you want the name to be changed easily or you want to minimise storage for foreign keys to the user table), an integer should be fine. Just how many users are you expecting to have? :-)

I would always use varchar for character data since I know that's supported on all SQL databases (or at least the ones that matter). I prefer to stay with the standards as much as possible so moving between databases is easier.

Answer 2

An int seems small? Are you anticipating having 2,147,483,648 users? (For the record, that's like 1 in 3 people in the world, or 400 million more than the total number of people who use the internet; or 5 and a half times more users than Facebook).

Answer: use an int. Don't overthink it. If you get in a situation where our old friend the 32-bit integer just isn't cutting it for you, you will probably also have a staff of genius college graduates and $5b capital infusion to help solve the problem then.

Answer 3

Your username field should probably be a varchar (you pick the length). If you have an id_user on the user table, I agree with the above answers that int (2 billion) will more than adequately cover your needs.

As far as you alphanumeric data types, it depends on how long you want it to be. For example, I think a varchar(255), or probably even smaller (personally I'd go with 50), would work for a username and password field. This chart might help you.

Binary would be for storing non-character data such as images. bit can be used for boolean values.

A couple other useful types are uniqueidentifier for UUID/GUID and xml for XML. Although you can also use varchar or text types for those too.

There's also nvarchar for Unicode in SQL Server and several date types (I'm glad they came out with date in SQL Server 2008 which doesn't include a time such as smalldatetime).