Which value to store in main table from lookup table. 2

[Moss100]

Hello,

I have a main table called tblproperties

It has a field TYPE

The values for the TYPE field come from a table called tblPropertyTypes

When storing a value in the field TYPE, I can either store the actual Value (i.e. house, flat etc) or the primary key of the table tblPropertyTypes.

I am not sure which is best:

If I store the actual Value, for example Flat and later wanted to change the wording from Flat to Apartment I would have to update all previous records. Otherwise if I just stored the primary key i.e. 2, it would save the updating, but be less clear at table level, during queries etc...

What is the best way to approach this?

Many thanks Mark

 

[SkipVought]

Hi,

If the terminology for any TYPE can change, without a doubt, store the KEY!

 

[Moss100]

I see that if I enforce referential integrity and cascade updates the values
Automatically update. This way I can store the value and if the value in the
Lookup table changes it applies the changes to the main table.

Bearing this in mind, do you think it is still better to store the key?

Many thanks for your early reply.

Mark.

 

[SkipVought]

That's what I'd do. But I'm more of a database user than a designer.

I'd defer to one of the MVPs in this forum.

 

[dhookom]

IMO, always use the primary key value. Also, [Type] is a bad name for a field. I would think it would be easier to understand and maintain if you used [PropType] or similar.

Duane
Hook'D on Access
MS Access MVP

 

[Moss100]

Many thanks