A quick question in normalization (Access 2010) (1 Viewer)

JoseO

Registered User.
Local time
Today, 04:33
Joined
Jul 14, 2013
Messages
72
Hi -

I know I can develop my db anyway I want but wanted to get some opinions.

Quick question:

Is it best to normalize first and last names? If I have two Joe Smiths, what does it matter if I have a field for the first name and the other for the last name? After all, the autonumber is what's keeping track correct? Would it be terrible db design to have the first and last in one field?

Thank you
 

Mark_

Longboard on the internet
Local time
Today, 02:33
Joined
Sep 12, 2017
Messages
2,111
Which would you prefer to sort by? First name? Last Name?

What having two field really allows you to do is easily order data in a logical manner and avoid users entering... strange data.

Joe smith
Smith, Joe

Sydney Taylor
Taylor Sydney

If its all in one field you can only guess what is what. If you have clearly defined fields it becomes MUCH EASIER.
 

isladogs

MVP / VIP
Local time
Today, 09:33
Joined
Jan 14, 2017
Messages
18,186
A lot of people do have both in the same field but it is bad practice.

Each field should only contain one item of data.
It is easy to concatenate two fields in a query to get the full name.
It is much harder to split a combined name especially when you get names like Anne Marie Jones or Tom Rob Smith where it's not always obvious which parts count as first name

I advise using two fields

BTW Tom is an author and Rob is part of his surname
 

JoseO

Registered User.
Local time
Today, 04:33
Joined
Jul 14, 2013
Messages
72
Thank you Mark. I completely agree with your assessment. It does make sorting much easier. The only annoying thing is running reports when the fields are separated.

For instance, if I concatenate my FN and LN to create a "FullName" field, I can't use this field to pull up reports by a person's name. Thanks again for your feedback.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:33
Joined
Feb 28, 2001
Messages
27,001
The answer to this question must come from you. But here is how you decide.

If all you are doing is tracking names and the meat of the database is entirely something else, it might be OK. Still might not. The question will be: How did you intend to use the field? If you will ever want to do a "Last name, First name" report then you must split the fields. If ever want to build a SEARCH form to look up someone, having the split fields can help you quickly narrow down the options.

If NONE of those actions (Last-name reports, searches) apply to what you will want to do, then it doesn't matter. But only you know your intent.
 

JoseO

Registered User.
Local time
Today, 04:33
Joined
Jul 14, 2013
Messages
72
You guys are awesome! Thank you so much Mark, ridders and Doc Man.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:33
Joined
Feb 28, 2001
Messages
27,001
if I concatenate my FN and LN to create a "FullName" field, I can't use this field to pull up reports by a person's name.

You can if you ask the question correctly. If you concatenate the name parts to form a full name field, that should only occur in queries anyway. You could easily do a search for a record ... "WHERE LastName = 'Smith' AND Firstname = 'John' ..." I.e. if you store the names together, you must search for them together - but if you store them separately, you can search for the combination of parts and it works just as well.
 

JoseO

Registered User.
Local time
Today, 04:33
Joined
Jul 14, 2013
Messages
72
Thank you Doc Man - will give it a whirl :)
 

Mark_

Longboard on the internet
Local time
Today, 02:33
Joined
Sep 12, 2017
Messages
2,111
For instance, if I concatenate my FN and LN to create a "FullName" field, I can't use this field to pull up reports by a person's name. Thanks again for your feedback.

Exactly, plus you can't be sure the USER entered the name in the proper order.

In a query (or for display) you can always use
Code:
=[FirstName] & " "
 & [LastName]

OR

=[LastName] & ", " & [FirstName]

For queries, replace "=" with "FullName: ".

Of all reasons to keep them separate, users putting in names in a different order than you expect is probably the biggest.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:33
Joined
Feb 19, 2013
Messages
16,553
another benefit of keeping first and last names separate is to do with performance.

To find the 'Smith's with records set up as a single name means using Like '*Smith' as a criteria. The use of the initial * means that the db cannot use indexing so to find all the records that contain 'Smith' means it has to use a sequential search which is significantly slower. Probably not noticeable on small datasets but larger ones can have a significant impact.

Searching for 'J*Smith' will be faster because the db can use indexing to limit the search to names starting with J.

as an aside, I often had a third field 'salutation' where you regularly communicate with someone - e.g. 'Jack' or 'Nettie' so you can personalise letters/emails etc. but still need to proper name for other reasons
 

Users who are viewing this thread

Top Bottom