Lookup Field Alternative

[Steve Finlayson]

I have read the comments about the disadvantages of using lookup
fields. They make sense. I am unclear how to accomplish the same thing
without them. Please refer me to some specifics or a paper that would
discuss how to work around the lookup fields.
I have a db that uses lookup fields to pull information from other
tables as the data is being entered.

I have a table Property that has Policy 1 through Policy10 as fields
and populate the data from a Policy table that contains the details
for various specific policy. Hence the Policy table links to 10 fields
in the Property table.

Q1 Is it necessary to define a link in the Relationship screen
between the Policy table and the Property when the lookup field is
used to select the correct Policy to enter into the Property field?
Q2 Do I need to create links from the Policy table to each of the
Policy1 - Policy 10 fields?
Q3 Is there a better way to set this situation up? Each Policy could
cover multiple properties and each property will have multiple
Policies covering it.
Thanks
Steve

 

[John Vinson]

I have read the comments about the disadvantages of using lookup
fields. They make sense. I am unclear how to accomplish the same thing
without them. Please refer me to some specifics or a paper that would
discuss how to work around the lookup fields.

Use Lookups freely. Use them routinely. Lookups are a very good thing
- IN THEIR PLACE!

The criticism of the Lookup field type refers to using Lookups IN A
TABLE. They are confusing and inefficient *in a table*. They (under
their alternate name "combo boxes") are routine and all but
indispensible on FORMS - where one should do all user interaction with
the data in your tables.

I have a db that uses lookup fields to pull information from other
tables as the data is being entered.

I have a table Property that has Policy 1 through Policy10 as fields
and populate the data from a Policy table that contains the details
for various specific policy. Hence the Policy table links to 10 fields
in the Property table.

This is an incorrectly normalized database structure. IT IS SIMPLY
WRONG. If you have a many to many relationship from Properties to
policies, implement it *as a many to many relationship* between two
tables, using a new PropertyPolicy table to resolve the relationship.
"Fields are expensive, records are cheap" - you should have ten
*RECORDS* in PropertyPolicy, with the PropertyID linking to your
current table and the PolicyID linking to the Policies table.

Q1 Is it necessary to define a link in the Relationship screen
between the Policy table and the Property when the lookup field is
used to select the correct Policy to enter into the Property field?

This is exactly why people HATE the Lookup Wizard. It deceives you
into thinking that you can create and use a non-normalized table like
this! To directly answer, no: one of the other flaws of the Lookup
Wizard is that it creates a relationship (EVEN IF THERE IS ONE
ALREADY!) and then conceals it from view; it doesn't show up in the
relationships window even though it's there.

 

[Tim Ferguson]

I have a table Property that has Policy 1 through Policy10 as fields
and populate the data from a Policy table that contains the details
for various specific policy. Hence the Policy table links to 10 fields
in the Property table.

No, the last sentence is wrong. The Policy field in the Property table
links to a whole record in the Policies table. It works like this...

No, wait a minute, the whole paragraph is wrong. Ten Policy fields should
really be ten records in a HasPolicy table. Each of those records contains
a pointer back to the Properties table and another forward to the Policies
table. Look at this:

Propertynum Policy
=========== ======
Fortnum Life57
Fortnum HandsomeG
Fortnum HighPressure
Marks Life57
Marks HandsomeTY
Marks Etc...


and you can see how this could be expanded to

Fortnum, 17 Fortinbras Drive, 028 4456-0909, Life57, Monthly, $123
Fortnum, 17 Fortinbras Drive, 028 4456-0909, HandsomeG, Monthly, $67.50
Fortnum, 17 Fortinbras Drive, 028 4456-0909, HighPressure, Yearly, $9.25
Marks, 309 West Mark Street, etc etc etc

Q3 Is there a better way to set this situation up? Each Policy could
cover multiple properties and each property will have multiple
Policies covering it.

Yes: see above, which is a bog-standard many-to-many relationship. In your
case, I guess it's likely that you have Long Integer Autonumbers instead of
the names that I used in the example above, but it should give you the
picture.

Oh, by the way, you asked about setting relationships. The point is that
you don't want to have any of these pointers pointing to nonexistent
policies (or properties). The heart of a DBMS is to take care of that for
you: so when you set Ref Integrity between Properties.PropertyID and
HasPolicy.PropertyNum, it just won't let you enter a value in the HasPolicy
that doesn't exist, and it won't let you delete or change a PropertyID that
still has HasPolicy records pointing at it.



Hope that makes some sense


Tim F

 

[Duane Hookom]

Having 10 policy fields is not a good idea. Each policy should create its
own record in a related table.
All data entry, viewing, etc should be done in forms where you can use combo
boxes.
You should create links/relationships to enforce referential integrity.