Best Practices Question

[GoodLife22]

Please see my sample DB, sometimes my verbiage is off and a sample may help. My DB has 2 sample tables. I want to know why one way is better than the other.
I am specifically dealing with the way I link the company name to the employee.

Sample 1 – Is a direct drop down from one table to the other.
Sample 2 – Is a link to the ID of the company via the combo drop down. So technically it is only showing the company, but storing the ID.

In all of the samples I find online Microsoft seems to go with the second sample style. So my question is this:

From a design element why is the second option so much better than the first?

I know the second option is better when a record such as company name changes you would not have a ton of orphan records tied to a company that no longer exists in tbl_COMPANY, but it also makes queries harder to write. See query sample1 and query sample2. So why is the second option the way to go?

Thanks.

 

[Kryst51]

See here for the evils of lookup fields.

 

[GoodLife22]

So are you saying that sample2 (I am assuming that sample2 is using the look up field method) is a BAD or WRONG way to do it. Even thou all of the microsoft examples are set up that way?

I seriously want to know which is better overall.

Thank you!

 

[Kryst51]

Yep that is exactly right. I have hear that it is OK, during the design phase to have them because it is easier to set up forms and such, but then you should go back later and remove the lookup from the field in your tables, but the combo boxes and such will remain.

I am not quite sure why Microsoft does that, only that I think I read on a thread here one day, that several people (MVPs) have asked for that to be changed but it never is. But I could be misremembering. But it is very bad practice and will cause problems for you later.

 

[Kryst51]

btw.... Both of your samples are wrong....

 

[GoodLife22]

OK thank you very much so far.

so if BOTH are wrong what is the correct way? Will you PLEASE take 2 minutes to make a sample3 the way you think it should be done and upload it? I would LOVE to see the BEST way to do it.

 

[boblarson]

Microsoft has never been about correctness with Access and database design principles. Many an Access MVP has argued with the development team about that. Their take is they want to make it as easy as possible for the end users (not true developers mind you). But what they don't seem to realize is that many of their "helpful" things are really not so helpful and can actually cause more pain when people start using them.

So, yes Lookup Fields at table level are not recommended by most people who do Access development professionally. It is a best practice to use lookups only at form level.

 

[Kryst51]

See attachment, I made changes to your sample two stuff- check out the sample 2 table, query and the form I made.

 

[boblarson]

OK thank you very much so far.

so if BOTH are wrong what is the correct way? Will you PLEASE take 2 minutes to make a sample3 the way you think it should be done and upload it? I would LOVE to see the BEST way to do it.

Here's the way it should be from my perspective. OOPS - missed a couple of things.

 

[boblarson]

Here's the way it should be from my perspective. OOPS - missed a couple of things.

Okay, fixed my error (forgot to take out the Company Text from Employees table). I'm sure it is similar to Kryst51's sample.

 

[gemma-the-husky]

Microsoft has never been about correctness with Access and database design principles. Many an Access MVP has argued with the development team about that. Their take is they want to make it as easy as possible for the end users (not true developers mind you). But what they don't seem to realize is that many of their "helpful" things are really not so helpful and can actually cause more pain when people start using them.

So, yes Lookup Fields at table level are not recommended by most people who do Access development professionally. It is a best practice to use lookups only at form level.

the other thing is, that by adding into access non-standard extensions, it may make it dfficult to upsize to a database that doesnt have such extensions.

another pointless example is the + sign you see in tables, that automatically expends a sub table