Foreign key and null value (1 Viewer)

Siegfried

Registered User.
Local time
Today, 01:32
Joined
Sep 11, 2014
Messages
105
Dear Experts,

I need your advice on a Foreign Key issue, let me explain with an example.
I have set up the following 3 tables:

EmployeeT
EmployeeID PK 1
FirstNameID FK Ward
MiddleNameID FK
LastNameID FK Peters
PreferredNameID FK Warre

FirstNameT
FirstNameID PK
FirstName

LastNameT
LastNameID PK
LastName

I haven't setup separate tables for MiddleName and PreferredName, instead I opted to created a relationship for each with FirstNameT.FirstNameID and pick a first name there. For example employee Ward (first name) prefers to be called Warre.

However, not every employee has a preferred name or a middle name.
What's the best way to set it up in a table? I created a relation so the for the other employees who don't have a middle name or preferred name the system asks for a value? But I can't set my Foreign key to null, that not correct.
Can you please shed a light?
Thanks.

Best regards,
Siegfried
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:32
Joined
Oct 29, 2018
Messages
21,358
Hi Siegfried. Is that just a sample table structure, or is that really what you're trying to use/create? I just haven't seen any database where there's a separate table for first and last names. In any case, a Null FK is "normal" (not bad), but a Null PK is not allowed. So, if an employee doesn't have a preferred name, then you can leave the field blank. That should be okay, in my humble opinion.
 

isladogs

MVP / VIP
Local time
Today, 00:32
Joined
Jan 14, 2017
Messages
18,186
Just use one Employee table for the above data with these fields:

EmployeeID PK
FirstName
MiddleName
LastName
PreferredName

You already have a PK field which ensures each record is unique.
There is no need to have separate tables and PK/FK fields for first & last names.

However to improve search speeds you should INDEX any fields used in searches such as LastName
 

Siegfried

Registered User.
Local time
Today, 01:32
Joined
Sep 11, 2014
Messages
105
Hi, thanks for your swift response and help.
I created those table to avoid double data entry in the names?
If I have 3 employees having both the first name Marc, then I'm entering that info three times in the database not? Shouldn't that be avoided? Perhaps I'm approaching that wrongly? I'm thinking in the same line of a cities table, you only enter a city once not double, so I'm doing same for names...
 

plog

Banishment Pending
Local time
Yesterday, 19:32
Joined
May 11, 2011
Messages
11,613
No, you are over-normalizing your data. Any table with just 1 real field of data (autonumbers aren't real data) do not need to exist.

There is nothing wrong with duplicate values in a table. So your name tables as well as your city field do not need to exist.
 

isladogs

MVP / VIP
Local time
Today, 00:32
Joined
Jan 14, 2017
Messages
18,186
Beaten to it again this time by plog ...

I've never seen it done for names.
I think you are trying to 'over normalise'
There could be thousands of each types of name.
To populate each of the tables would be unnecessarily complex.

Doing that for cities MAY be appropriate depending on the data you have.
However if storing employee addresses (which should be in a separate table) I wouldn't reference a city table for that purpose, nor a postcode table etc.
That way lies madness!
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:32
Joined
Oct 29, 2018
Messages
21,358
Hi, thanks for your swift response and help.
I created those table to avoid double data entry in the names?
If I have 3 employees having both the first name Marc, then I'm entering that info three times in the database not? Shouldn't that be avoided? Perhaps I'm approaching that wrongly? I'm thinking in the same line of a cities table, you only enter a city once not double, so I'm doing same for names...
Hi. You may have a point, but you may also be taking that point too far. Normalizing a database's table structure is a balancing act. You can normalize to your heart's content, but efficiency may suffer. Sometimes, you have to denormalize a little bit to get a good working solution. So, let's examine your argument a little bit. You say a first name shouldn't be entered more than once, correct? If so, you might have the following in that table.

tblFirstNames
JOHN
MARK
ANDREW
LUKE

You could have a numeric PK field in the above table, but since the first names are supposed to be unique anyway, we can just use it as the PK; hence the above table doesn't have an ID field.

Now, in your employee table, you might have some name combinations like:

tblEmployees
MARK JONES
MARK SMITH
MARK EVANS
etc...

Do you see my point? In other words, wouldn't you be entering first names more than once anyway? Once in tblFirstName and then multiple times in tblEmployees?
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 00:32
Joined
Jan 14, 2017
Messages
18,186
On behalf of all of us, you're welcome.
Remember you may have 3 John Smiths all from London but as each will have a unique EmployeeID, Access cannot confuse them
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:32
Joined
Feb 28, 2001
Messages
27,001
Siegfried,

Here is a different view of the rule that might help you to understand why you were told that you are over-normalizing.

When you have a record in table X AND table X has a primary key, then the rule is that every field in that record MUST depend on the PK for uniqueness. But the reverse is NOT true - that the PK depends on any of the field values for uniqueness.

Therefore, you can have 3 John Smiths (with different PKs) because the PK does not depend on the name. The name depends on the PK. You can index the fields that make up the name (with DUPS ALLOWED) but that only matters when doing a search by part of a name.

In your "name lookup" tables with a PK and a name, if the point of the table was to assure uniqueness of that name, you already have it if the name is set for "NO DUPS." In that case, the PK would be redundant because the name would already be unique and would thus be a candidate key (i.e. eligible for being the PK). And that would leave you with a table of names with no duplicates for which the name ITSELF would be the FK from the employees table to the first name table. But that would mean the name is in the employees table - in which case why bother to have the external table?

That may sound a little convoluted - but that is actually the point. What was it that Sigmund Freud once said about dreams of smoking? Sometimes a cigar is just a cigar. And by corollary sometimes a name is just a name.
 

Cronk

Registered User.
Local time
Today, 11:32
Joined
Jul 4, 2013
Messages
2,770
I agree with the universal view expressed here that what is proposed is over-normalization. BTW, if this approach were to be followed, rather than separate tables for first and last names, there should be only one names table with first, last, middle names included together.

Doc, wouldn't your argument in your second last para, apply equally to lookup tables?
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 20:32
Joined
May 21, 2018
Messages
8,463
In the US Census there are about 150K commonly used surnames and 5k commonly used first names. Assume you add in the uncommon ones. Lets say about 2 million surnames and .5 million first names. If you are logging the US population, I think around 350 Million the reference table storage requirement would far exceed the storage saved by using a numeric key to a reference table.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:32
Joined
Feb 28, 2001
Messages
27,001
wouldn't your argument in your second last para, apply equally to lookup tables?

Yes, if the only thing you are looking up is a relatively short name AND the issue is only the name. When the lookup table is doing more than that, then there remains a valid reason to have one.

For instance, if your lookup table contains ONLY the valid values from among a larger set of choices, then that is a reason to have a lookup table, which is actually a "constraint" table or an "IN (list)" table. (E.g. a table that lists the names of the companies with which you do business, which is a subset of the names of all known companies with which ANYONE could do business.) If this list is driving a combo box then it CERTAINLY makes sense.

If the lookup includes extra info besides just the name, then it is serving an extra purpose, so a separate table makes sense.

My argument strictly and only applies to tables where the thing being listed is set for NO DUPS (thus unique) and is the only field in the table AND isn't driving a combo/list box.

But it is a good question, Cronk.
 

Users who are viewing this thread

Top Bottom