Naming a field that will serve as link

[Valery]

Hi everyone! Just want your opinion on this "dilemma", lol.

I have a UnitType field. It is represented as:

1B (stands for 1 bedroom)
2BI (stands for 2 bedrooms, inferior level)
2BL (stands for 2 bedrooms, superior level)
2BA (stands for 2 bedrooms with special amenities)
...
for a total of 7 types.

This field will be used as a link to many tables. Should I have a lookup table? With the link as a single digit? Like:


1 - 1B - 1 bedroom
2 - 2BI - 2 bedrooms...

And would that single digit be an autonumber?

My main concern is new admin users and not having them to remember
1 = 1 bedroom, 2 = 2 bedrooms with... and 3 (THREE) = 2 (TWO) bedrooms high level... when doing queries for example.

Much appreciate your expert input!

 

[pbaldy]

I would certainly keep the values in a table, so over time they can add new options as necessary. I'd use a key field like you describe, though it isn't mandatory. You can use a combo box to let users choose the type, and the key field can be hidden so all they see is the user-friendly value.

 

[GinaWhipp]

You should use a Lookup table and you should use Autonumber. Your Users would be selecting from a Combo Box with the Autonumber being hidden they would only see the selections, i.e. 1 bedroom, 2 bedroom, etc. They should not be having access to the creating queries their interaction should be via Forms.

 

[Valery]

I totally understand. But once I let go of this database, there will be 1 administrator that will handle it and, yes, he will be wanting to do queries and so on. It will be HIS database. He may create new forms, new reports down the road. As well, the number of bedrooms in the units is certainly VERY unlikely to change! And being a very poor COOP, don't think they will be building new housing, let alone 4+ bedrooms (in answer to pbaldy). So that is not my concern.

Of course I am creating forms with dropdown for input where the key is hidden and so on. Was just trying to think ahead.

The admin guy is very familiar with the 1C, 2CI, 2CS... reference. It is everywhere in his Excel spreadsheets and current printed reports.

I personally find it cumbersome to pull or create IIF statement with such field names. But I am trying to do what is best for the client - not for me.

So, will it be easier for him to stay with names he is familiar with or remember what the assigned 1 to 7 numbers correspond to...? Especially that we would have numbers referring to numbers (id 3 would be a 2 bedroom, id 4 would be a 3 bedroom... of which type... so may have to refer to the lookup all the time). Is it worth a lookup table...? Currently, the field is already set up with the values for easy, correct input.

 

[GinaWhipp]

The best thing you can do for the Client is build a proper normalized database. Whether he comes back to you or does it himself giving him a database that isn't properly done will make more work for either.

You will not need IIf statements you will just use joins to those look-up tables.

 

[Valery]

Got it! Thanks! Answers my question perfectly. Gina - you rock!