1
First Name 
Phone Number
Email 
Address

For the above values, what would be the 

Type
Length
Collation
Index

And why, as well as - is there a guide somewhere that I can use to determine these answers for myself? Thanks!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Steven
  • 687
  • 1
  • 10
  • 27
  • 3
    ...............??? [how to ask a good question](http://stackoverflow.com/help/how-to-ask) – A.B Mar 21 '15 at 18:58
  • This question is too broad for this site. Could you show what you have tried and what the problems were? – Schwern Mar 21 '15 at 19:07
  • I honestly do not know how to make the question plainer. For names, should I use varchar / text/tinytext/blob etc. What is the typical name length. The same with phone numbers. Should I use int / string / etc. and so on... I followed your link, and the only thing that I found that might have been confusing for the part of anyone reading, was that I was not using inline markup for the listed items(I will note for future). Why do you not point out what makes this question a particularly bad one? Thanks. Edit : Thank you Schwern, for your enlightening links. – Steven Mar 21 '15 at 19:11
  • For one thing, it's too broad. This is not a replacement for searching. Where is your effort in all of this? "I was thinking of using int for a First name column, but then I got this sql error when I tried to insert my name. What could be wrong?" - a more specific question, with some effort behind it. – Mackan Mar 21 '15 at 19:17

2 Answers2

0

This is somewhat dependent on database platform and the underlying requirements. These are typically all nvarchar or varchar types. You are the best at knowing the appropriate length given your requirements. For collation you can usually use the default but again it depends on your situation. I suggest reading up on indexes to address what indexes you need.

I agree with the others, you should do some reading on beginning SQL. I also suggest just implementing something and you can always change it after the fact. You'll learn a lot from just trying it and running into issues that you will then need to solve.

Good luck!

BryceH
  • 2,740
  • 2
  • 21
  • 24
0

For names, should I use varchar / text / tinytext / blob? What is the typical name length?

  • If you're only going to support "normal" Western European / English names, then a (non-Unicode) varchar type should do
  • If you need to support Arabic, Hebrew, Japanese, Chinese, Korean or other Asian languages, then pick a Unicode string type to store those characters. Those typically use 2 bytes per character, but they're the only viable options if you need to support non-European languages and character sets.

As for length: pick a reasonable value, but don't use varchar(67), varchar(91), varchar(55) and so forth - try to settle on a few "default" lengths, like varchar(20) (for things like a phone number or a zip code), varchar(50) for a first name, and maybe varchar(100) for a last name / city name etc. Try to pick a few lengths, and use those throughout

  • E-Mails have a max length of 255 characters as defined in a RFC document
  • Windows file system paths (file names including path) have a Windows limitation of 260 characters

Use such knowledge to "tune" your string lengths. I would advise against just using blob type / TEXT / VARCHAR(MAX) for everything - those types are intended for really long text - use them sparingly, they're often accompanied by less than ideal access mechanisms and thus performance drawbacks.

Indexes: in general, don't over-index your tables - most often devs tend to have too many indexes on their tables, not fully understanding if and how those will be used (or not used). Every single index causes maintenance overhead when inserting, updating and deleting data - indexes aren't free, use them only if you really know what you're doing and see an overall performance benefit (of the whole system) when adding one.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459