Why use relationship instead of LOOKUP? 4

[may1hem]

I am building a new database in Access XP to store products, suppliers, customers, orders, etc.

When new products are added, I want the user to be able to select which supplier the product comes from, so in the design view of the PRODUCTS table I created a foreign key field called 'SupplierFkey' and asked it to be a Combo Box control looking up its value from the SUPPLIERS table. This seems to work fine.

What bothers me is the fact that in the Relationships window, no relationship is automatically created. Do I need to manually create one? If so, why would I need to create a relationship when the Lookup feature does the job? What benefits would I get from adding a relationship? If you think that I should create a relationship, why would I need to use the Lookup feature? Wouldn't the user be able to select a supplier if there was no Lookup feature, but there was a relationship set up between the PRODUCTS and SUPPLIERS tables?

Any ideas?

Thanks,

May

 

[SgtJarrow]

First, since you are just starting out with your design....STOP! You are probably going to set yourslef up for failure later on down the road.

Most of the developers I know, and many here, will tell you that the lookup feature of tables is not the way to go. It will lead you to trouble. There are many documented cases of improper displays, and faults created from using this option.

The correct method you seem to have already begun. IN one table you have the items as a primary key. They are tied to a foreign key in another table. Open the database relationship window and create your relationship here. Depending on the option you select in the relationship window, you can ensure integrity, and auto update and delete all related table data.

This all ultimately leads you to the form that you will be doing data entry through. (Yes I said form.....once developed, a table should rarely be accessed directly by the user.) The form can include a bound combo box to the field that is the foreign key of the table, and the row source for the combo box is set to the primary key in the other table.

Do it right the first time, and you won't be stuck pulling out your hair to fix it later.... Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[may1hem]

Thanks for your reply, Robert.

Based on what you said, I performed a search here and found no excessive problems related to Lookup fields. Here is a long thread on the topic:

Thread name:
"Death to Lookup Fields (am I right?)"
Thread number:
700-295181

People seem to have differing views about Lookup fields, mostly positive it seems, contrary to your own opinion. The only issue I have seen is related to performance, and that is pure speculation. Can you point me to any specific issues with Lookup?

Hmmm... I understand the traditional way is to use direct relationships between the primary key in one table and a foreign key in another table. I am still then confused about the benefits of using this traditional relationships over using the Lookup feature.

Thanks,

May

 

[SgtJarrow]

May,

The thread you found is the most specific on this topic. I will take a look-see in a bit and try to find some more. But I will try to give a couple examples of the benefits of traditional relationships versus lookup fields.

First, think to the future....you may need to update the lookup field. If the data is in a table, you simply open the table and add, remove, or change any of the values. If the data is in a loopup field, you have to go to the table, design mode, properties for the field, select the appropriate tab, find the correct property and edit this manually.....can't forget that every entry must be enclosed in double quotes and be followed by a semicolon....all get very long and trciky, and god forbid you make one small mistake...may take you a while to find it. With the table method, you simply open the table and add them to the botom, delete them, or edit thiem....much simplier.

Second, and this ties back to the first, is data integrity. In an ideal database, no duplicate or calculated data is stored in the database. Duplicate data includes exactly what you are looking at. INstead of using a refernce to one existing record, you are inserting that record information into every other record....it will cause you database to grow much faster. You also can't index on the field properly, so it will slow down search strings and queries.

Finally, updating/deleting. Image you have one entry - "Dog". Under this entry you are putting any dog in your database. You later discover you need to call dogs puppies. Under the lookup system, you will have to go to each and every record and fix it. Under the relationship system, if you have enforce referential integrity, the updates will be applied to each record immediately....no need for you to do it. The same happens for deletions....you no longer need any dog data in your database. So you go to the appropriate table and delete the record....any record with dog in that relational field (if you have set the integrity to enfore this) will also be deleted. It makes things much easier to maintain....

Of course, all of the above statements are my opinion. I only hope they can help you make an educated decision on you situation. SOme may tell you Lookups are okay....and when I began programming, I used them a lot....but as I became more experienced, they started to fall apart for me....they just didn't support my development....users couldn't update them without access to the table design, etc. So I have since decided to always use the relationship method....It may seem like overkill, but I use this method for even a field that only has two options....Why? Two today will be fifteen tomorrow.......

Pick what is best for you.....my intent is only to provide your with info.....good luck! Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[MichaelRed]

... If the data is in a table, you simply open the table ... [/]

Not my first choice.

I would generally provide a FORM for all data manipulation. Try to insulate USERS from the db/tables. Using bound forms along w/ the much maligned - but qute useful secrity permits the tracking (audit trail) for 'who done it' secnarios as well as making changes easier for users.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[SgtJarrow]

MichaelRed,

I agree wholeheartedly....As I have stated time and again.....once you develop a table......if should only be access through a FORM. You should NEVER work from a table driectly...

I was merely trying to illustrate the differences between updating a lookup filled field and a relational filled field.....one involves much work in the table design mode (which the users should never be able to do), the other is a simlpy data addition/deletion/change......

Thanks for pointing that out.... Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[LittleSmudge]

The general message from

Thread name:
"Death to Lookup Fields (am I right?)"
Thread number:
700-295181


is that lookup fields are fun little ditties to amuse amateurs but have no place in profreesionally developed databases.

They are a definate negative influence on the maintainability of the database especially when someone who was not involved in the original design is trying to do the maintenance.
On he other hand they provide no positive, useful benefits to a professional DBAnalyst.


G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D

 

[montrose]

mayhem1, Echoing mstrmage1768 and LittleSmudge, look-ups should not be used in a tbl, but in form design based on a look up table. If you put the lookup field in your tbl and develop reports, the fields will continue to be lookup fields in reports (obviously makes no sense-can't edit data in a report!!). Lookups in tbls can actually end up hiding the key values you really need to see. A real mess later on when you're trying to find/fix data bugs. The 'feature' was designed for anyone using Microsoft products to 'easily' build a database. But fundamental normalization rules for relational database design indicate that storing these values within a table as a lookup field does not reult in normalization-will result in a headache/hassle down the road. montrose Learn what you can and share what you know.

 

[may1hem]

Thanks for your advice, guys! But...

I disagree that, in general, new features that are created to make building databases easier for newbies is a bad thing. Why? In my opinion, if a feature makes life easier and makes development faster then it's a great thing.

No offense LittleSmudge, but saying that a new feature has no place in professional databases just because some developers say so is not going to persuade anyone. Regarding maintenance, Lookup fields are now dead easy to use so if a database is set up as I describe below then it would be simple to manage, even by someone other than the original developer.

I hope I don't sound difficult, but as you must understand, no-one will just accept that something shouldn't be used just for the sake of it. I want to get to the bottom of this!

I will investigate Montrose's suggestion that the table LOOKUP may cause problems with reports, and post a detailed report (ho-ho-ho) on my findings.

I disagree that LOOKUPs take more effort to maintain. Let's take, for example, the PRODUCTS and SUPPLIERS tables. The PRODUCTS table contains a foreign key field that stores the primary key value of the related supplier. When I first create the PRODUCTS table, when I create the suppliers foreign key field, I tell it to look up the value from the SUPPLIERS table. Here are the settings used:

Display Control = Combo Box
Row Source Type = Table/Query
Row Source = tblSuppliers
Bound Column = 1
Column Count = 2
Column Heads = No
Column Widths = 0cm;5cm

The other settings are left as the default. So, when I open the PRODUCTS table or a form based upon it, and then click on the Supplier combo box, I will always be presented with an up-to-date list of suppliers. I don't have to, as mstrmage suggests, enter each new supplier in a manually delimited list.

The 2nd point that mstrmage makes is that Lookup fields may compromise data integrity and the field will store full record information. The Supplier foreign key field in the PRODUCTS table stores only the primary key as it is bound to column 1 (assuming column 1 is the primary key) which is the minimum data required.

Indexing should work fine with Lookup fields as they are stored in tables.

mstrmage is right about Lookup fields compromising data integrity. If the primary key value of a supplier changes, the Supplier foreign key value in the PRODUCTS table does not change. It stays at the original value, causing orphans (is that the right term?). In many Access databases, where the primary keys of all tables use the incremental Autonumber feature, the primary key will not change and so the Lookup feature will continue to work fine. Though I suppose we're still left without cascaded deletes.

Ok, ok, you win! I will try working with relationships between tables and use Lookups only in forms.

By the way, a friend has suggested that I shouldn't use relationships at all, but instead I should define what I want to move via SQL queries. Is there any merit in this?

May

 

[may1hem]

Actually, we can't get past stage 1 of the Report Wizard when we ask it for a report based on just the PRODUCTS and the SUPPLIERS table fields. The wizard complains that the tables aren't related and won't let us go any further without creating a relationship between them.

So relationships it is!

May

 

[LittleSmudge]

May

A few points that will help you get your head around this issue.

The ONLY person that should EVER see your tables in their naked for is YOU ( and any Developers that come after you. )

Users should be protected from tables ( NO, sorry - TABLES should be protected from Users. )

Lookup tables as part of the database schema are FINE and a fundimental part of RDBs that we couldn't do without.

Combo Boxes on Forms to allow users to select entries from LookUp tables is also fine - vital.

However, the problem with Look-Up FIELDs is that it means the tables LIE to you.
When, as a Developer, ( looking at the table to see what is going on and how to modify it to introduce some new feature that the client wants ) the table shows data in the records that actually DOES NOT EXIST within the table.
Yes, it is possible to work out what is going on and why - but it all adds time, potential confusion and complexity - for NO GAIN AT ALL.


These are the reasons that Look-up FIELDS have no place in a professional database design.



'ope-that-'elps.

G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D

 

[MichaelRed]

Actually, we can't get past stage 1 of the Report Wizard when we ask it for a report based on just the PRODUCTS and the SUPPLIERS table fields. The wizard complains that the tables aren't related and won't let us go any further without creating a relationship between them.

There IS a difference between setting up RELATIONSHIPS and Relating fields from recordsources in a query.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[may1hem]

MichaelRed: "There IS a difference between setting up RELATIONSHIPS and Relating fields from recordsources in a query."

Er.. I don't understand. Perhaps you could elaborate?

Are you saying that 2 tables might not need a relationship in the standard Relationships windows, but we might want to create a relationship between them in a query? If this is what you are saying then would you please explain why?

Thanks,

May

 

[LittleSmudge]

Are you saying that 2 tables might not need a relationship in the standard Relationships windows, but we might want to create a relationship between them in a query?

Yes that is EXACTLY what Micheal is saying.


Relationships in the Relationship window of the database allow the developer to define global relationships that ALWAYS exist. These can be used to maintain Referential integrity - ( to aid data reliability etc. )

Any query created in the QBE grid will then automatically establish and display those links when you are in query design mode.


HOWEVER.
If two tables are not linked in the Relationship window then you can still establish a join in the QBE grid - ( or by directly writing the SQL ). This link is then valid only for the query that you are working on at the time - and does not affect any other relationships or other queries or other SQL statements.

Any timne that you open a RecordSet in code like

rst.Open "SELECT tblA.*, tblB.Field1, tblB.Field2 " _
& "FROM tblA INNER JOIN tblB " _
& "ON tblA.FeildA1 = tblB.Field2"

you are creating a temporary relationship between the two tables that does not affect the Relationship window.



'ope-that-'elps.






G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D