Your solution is basically sound, but there are some
things worth noting:
- You really don't want to have a Name field if
you have a last name field -- what if someone changes
their name? It's safer to eventually drop the name
column entirely, when you've split it to first and
last names.
- Sometimes people's names arn't quite as easily parsed
as that. What if someone has a first name of "Mary Anne"
or a last name of "Simon Broquard"? There's not really
a good way to deal with those situations except flag
them and fix them by hand with domain-specific
knowledge (e.g. ask them)
- You might be able to do this more efficiently by
writing a stored procedure (not sure if MySQL supports
them though), so that all that data won't need to flow
woth ways from/to the database. OTOH, ~5000 data
entries is perhaps not enough data for this to be a
major concern
So, in sum, don't split name to name and lastname, split it to
firstname and lastname, and remove name. Be careful that
your splits don't encounter oddities. I hope this helps.