Guys,
I have a general question about designing databases for large data sets.
I was speaking with a colleague about an application we're preparing to
build. One of the application's tables will potentially contain 2 million
or more names, containing (at least) the fields first_name, last_name,
middle_name and prefix.
A common lookup the application will require is the full name, so prefix +
first_name + middle_name + last_name.
My friend's suggestion was to create a "lookup field" in the table itself,
which would contain a concatenation of these fields created during insert.
So, for each record, we'd having each individual field and then a
full_name field that would contain the combination of the ind. fields.
His argument is that this will make lookups in this manner extremely fast
and efficient.
I agree with his assertion, but get the feeling that this is sort of an
ugly design. Would a compound index on these fields really be less
efficient?
Thanks for your help!
John