Dumb design question...

[bassman197]

Hi,

I'm working with a database that I didn't design (but have modified since I worked here). As I found it, everywhere a person's name is required (client, employee) has two fields, first name and last name. Is this a standard design practice to aid in searches? an indexing issue?

Sometimes, the client isn't a person, but a company, in which case we are instructed to enter the company under the last name field.

It seems it would be easier to just have one field named client, where client is one string; if the client is a company, then type "Amalgamated Widgets, Inc.", if the client is an individual, then type "John Smith".

Likewise, "employee" would be one field and entered "John Doe" as one string, instead of having to enter it in two fields (first name, last name).

I'd like to get some feedback, before I make such a sweeping change. It would simply future entries, but would take some time to modify the existing ones. Any thoughts would be appreciated...

 

[pbaldy]

It's generally easier to join fields together than split one apart, so the 2 field solution is pretty common, and what I generally do. If you were somehow certain that you would never want the first or last name separately, you'd never want to sort on last name, etc, then perhaps 1 field would work.

 

[boblarson]

Personally, I would keep the name separated. I don't trust users to say whether they will never need it split so it is much easier to keep them apart and concantenate when needed rather than trying to split apart (names like O'Brien, Van Winkle, etc. can kill you).

 

[HiTechCoach]

Hi,

I'm working with a database that I didn't design (but have modified since I worked here). As I found it, everywhere a person's name is required (client, employee) has two fields, first name and last name. Is this a standard design practice to aid in searches? an indexing issue?

Sometimes, the client isn't a person, but a company, in which case we are instructed to enter the company under the last name field.

It seems it would be easier to just have one field named client, where client is one string; if the client is a company, then type "Amalgamated Widgets, Inc.", if the client is an individual, then type "John Smith".

Likewise, "employee" would be one field and entered "John Doe" as one string, instead of having to enter it in two fields (first name, last name).

I'd like to get some feedback, before I make such a sweeping change. It would simply future entries, but would take some time to modify the existing ones. Any thoughts would be appreciated...

Step away form the keyboard. DO NOT combine the fields.


I would avoid combining the fields. See this example on how to handle it as separate fields:
Contacts -- Names, Addresses, Phones, eMail, Websites, Notes

I have learned this lesson the hard way. I combined the fields. I did not have the WWW to help me back then or I would have never do it. If I had studied the "rules of data normalization" it will become clear why.

 

[gemma-the-husky]

one obvious reason for having a separate surname is that you can then sort/filter on the surname

also with separate fields you can present the data in a choice of ways - which you cant do if you restrict the entry to a single field (note you can also add a style (Mr, Mrs etc) in another field.

J Smith
John Smith
Smith, John
Smith J

 

[Mike375]

It is extremely easy to join fields whether in a query or unbound text box. I do it all the time, one reason for example being for mail merge or inserting data into Word Bookmarks.

HOWEVER, the raw data, that is, what is in the table is in different fields.

One of the more common questions on this forum is......how do I split this data into different fields?....and as others have already mentioned it be a real pain to do..

It seems it would be easier to just have one field named client, where client is one string; if the client is a company, then type "Amalgamated Widgets, Inc.", if the client is an individual, then type "John Smith".

Since a "FirstName" field would be null for company name you then have an easy way to break up the records accordingly.