2

There's an old question that asks this very thing, but a lot has changed in databases and unspoken standards.

I generally live by the rule as to never make a field size text or memo, even if it speeds up the database. Primarily because someone could flood and possibly hack the db if there are no restrictions in the input interface.


That said, what is the general practice these days for name fields in a table?

CREATE TABLE foo (
     name_first  Varchar(64) Not Null
   , name_middle Varchar(64) 
   , name_last   Varchar(64) Not Null
);

I think is my default, but how are others coping with foreign names and more than 3 names?



Community
  • 1
  • 1
vol7ron
  • 40,809
  • 21
  • 119
  • 172
  • 1
    For names `64` should be plenty, unless it's something like a prefix and you can be sure less will be enough. I would always add restrictions to the input fields though, so the user knows that they can't enter a full sentence or whatever if there are only _x_ characters available. That's always better than entering something and finding out later that only part of it was saved. And if someone has (and remembers) a 64+ character name, that's just bad parenting. – Alec Oct 04 '10 at 14:54
  • 2 years is not exactly old in that field... I don't think any country has started forcing names on its citizens in the last two years... – pascal Oct 04 '10 at 15:39
  • 1
    ... however, which charset are you using? Being able to store long names is cool, but being unable to store names in chinese, japanese, arabic... is not. – pascal Oct 04 '10 at 15:41
  • Read a little more about [names](http://en.wikipedia.org/wiki/Personal_name)... for example, I didn't know that "Teller" does not have a `name_last`... Maybe a 200-characters `name` column is more flexible... – pascal Oct 04 '10 at 15:43
  • [@pascal:](http://stackoverflow.com/users/202367/pascal) **(1)** I italicized for close-but-not-quite empahasis, but two years is a long time, especially considering social media and networking today. I also don't consider that question to be well answered. **(2)** Why would you consider storing names in foreign languages to not be "cool"? **(3)** I know there are people out there w/ no given name, esp. many African cultures, I consider those cases to be rare and thus I'd take email requests in those cases. Having one `name` column makes it more difficult to search by a name part. – vol7ron Oct 04 '10 at 16:49

2 Answers2

1

That should be plenty. We have millions of names from all nationalities and our longest lastname is 29 characters. I would add a personalsuffix field as well for Jr. Sr. III etc.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • That's interesting, how did you acquire these names? 29 is pretty long! Additionally, what did you set your field to be? In most databases these days there is no pre-allocation of space, thus `varchar(30)` has the same performance impact as `varchar(255)`. Conversely, databases do see a performance gain in disambiguating the field size (`text` is better performing than `varchar(n)`) due to less computational overhead. My primal concern is with foreign names, including multi-worded names, which are common in hispanic names. – vol7ron Oct 04 '10 at 17:00
  • 1
    We import name data from our clients' databases and include many many foreign names as our clients are international companies. Our field is very large as we don't know what we will get from a client, but 50 seems a reasonable choice. You can always make it bigger later if you need to. Speaking for SQL Server, Varchar(max) is a poor choice as there are indexing issues and Text is deprecated and should not be used at all. – HLGEM Oct 04 '10 at 17:11
  • I'm coming back to this and thinking about internationalization, where some countries have many middle names, or their last names are more than one word. I'm not sure whether to just throw it all in one field or even have a child table for the name and position of the name. – vol7ron Aug 23 '11 at 20:42
0

My theroy is, is better to have a lot more than to need a few. why not to use varchar(100)?

Chocolim
  • 54
  • 1
  • 2
  • 2
    I like to think about portability and consistency. Keeping something consistent between databases is ideal. `100` is a consideration. I also like to keep things smaller because with enough bytes, someone could possibly find a way to do something malicious on the database. – vol7ron Oct 04 '10 at 18:39
  • Additionally, if you're designing the system to allow 100 characters, then your input fields have to allow 100 characters (otherwise, what's the point). And while it's quite possible to do this without causing the fields on the screen to get unwieldy, it does create extra work if developers are in the habit of letting the database control the display size of the field. – Justin Cave Oct 04 '10 at 22:25
  • 1
    The developers should never let the database control the display. And they should always clean everything before update the database. I always use varchar of 15, 50, 100, 200. Because i can use the same maxlegth in my controls, and i always remember the size – Chocolim Oct 05 '10 at 12:57