Lookup table Confusion

[pablavo]

Hi everybody

I have been unsure about this topic and want to finally be sure.

When I create a lookup table I don't include a PK; I just have the one field. Therefore I don't include an FK within the Main table; I include the same field name as the field name in the lookup. "sector Details" in lookup and "sector Details" in Main.

Am I better to make up these lookup tables and use a PK and then have the FK within the Main Table? Is this easier or trickier to work with? And will it cause any problems if I did include keys? for example, providing combo boxes as search criteira for forms using the FK/PK as bound fields?

I know it's a bad idea to have lookup fields in tables which I'll avoid. But this means if I'm updating in the back end I'll only see a table full of keys and not the field name!

Hope someone can help because I've been getting different advice since I started on Access including Professional books using lookup fields.

Thanks.

 

[Dennisk]

All lookup tables should have a primary key. if you store the full text , it is wasteful of space, it is un-normalised and if you wanted to change the value say a spelling mistake or a re-wording then you need to apply an update query against the table where the value is used.

 

[Simon_MT]

I prefer Lookup Tables to have a "short key" rather than a meaningless number. If I want to find a specific Artist anyhere in the Database they have a meaningful "short key".

I would certainly PK the Foreign Tables and also index any reference to those Foreign Keys in other tables.

Simon

 

[Rabbie]

I prefer Lookup Tables to have a "short key" rather than a meaningless number. If I want to find a specific Artist anyhere in the Database they have a meaningful "short key".
Simon

The important thing is that there is a PK for each record. It is a matter of personal preference whether you want a meaningful key or an autonumber etc.

 

[Pat Hartman]

Even you should not be updating table directly in tables except in rare circumstances. If you need to go that route, just create a query that joins the main table to each lookup table. That way you can see both the text value and numeric (or short code) value.

Lookup tables should have primary keys and referential integrity should be enforced between the main table and the lookup table. This will prevent bad data from creeping in.

 

[pablavo]

Thanks alot for the help folks...
That clears alot of confusion

Edit:

Actually, I have two more questions

If I use the ID keys so that the main table is using FKs rather than names, I take it that I set the Bound column on the form combo to the ID but hide this so that only the actul name is visible so that when a user adds the entry they will only see the actual name and not the ID?

One more thing... in reports, If i'm using the Main table as the record source, and I use the Lookup table ID's from the main table, would I adjust these fileds to be combos so that the only the actual name is visible.? If I don't do that I would have a report full of ID numbers.

Thanks

 

[Simon_MT]

On the Forms combibox all you need to do is set the first (ID) column = 0. With the reports, I would always use a Description and in most cases I would have a Query for Screens and a Query for the Report, the report would link into the relevant Foreign Tables to get the Descriptions.

Simon

 

[Simon_MT]

Here is an example that best describe what lookup tables are used for.

When I started one project the Artists' Names were in free-formatted text fields. This lead to a variety of typos. In addtion, whats the point of typing Michael Rothenstien umpteen times when a linked table with this information will suffice. Likewise, if an Artist become an RA, you change it once and this permeates through the entire database, rather than having to change all the reiterations of the Artists Name, one at a time, this is how the previous system worked.

In the Report, I would always display The Artist Name rather than the Artist ID or Rerefence.

Yes, tables don't need IDs but if you embed the Artist Reference ROTH rather than ArtistID 66. From a data analysis point of view, the Artist Reference is more relevant and I don't need to refer to the Artist Table to establish that Artist 66 is in fact Michael Rothenstein. From a uses point is you have a combi-box based on Artist Name, there are 16 Michaels with Rothenstein number 12. Within Mediums there are a 134 variations to using oil based paint, the most common is Oil on Canvas, this would be 60 in the list. so Medium also have abbreviated Reference OOC for Oil on canvas. This is easier for the users and is not a technical issue.

Whatever method you choose think about the user, which is the quickest, I don't think there is an emphatic answer about the approach to lookup tables however their importance is to remove the need to reiterate descriptors that are are commonly used.

Simon

 

[pablavo]

Thanks Simon! this has all helped and now that I have far more info than before I can put this issue behind me.