Which value do I store in the primary table from a support or lookup table? 4

[Moss100]

Hello I am having a bit of trouble trying to decide what value to store in my primary table.


I am writing a property database.

So for example I have two tables: Client and Property

I understand that I will want to store Client_ID in my Property table setting up a one to many relationship.

If however I want to store the clients title using my support table (as below), would I store Mrs or 3.

Client_ID: Value:
1 Mr
2 Ms
3 Mrs

If the answer is I should store the ID, is it ever appropriate to store the value rather than the id number.

The more I read the more confused I am getting!

I also read that it is bad practice to have a lookup set at table level - although on MS Access templates many of their examples do just this.

Help and guidance very much appreciated.

Thanks Mark

 

[SkipVought]

Hi,

I hope your Clients are more than 3. I'm having trouble relating what you REALLY intend here. Are you trying to maintain control on TITLES? That's not Clients!

The thing is, if Miss Scarlet marries Professor Plum and he become a plumber, and you have Miss and Professor in various tables, there's a problem, isn't there?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Moss100]

Hi thanks for responding.

To clarify.

Lets say my clients tables has 100 records in it.

Each clients name is built from the following fields.

Title
FirstName
LastName

As the title is often repeated information it seems sensible
to offer the user a predefined list via a combobox.

Thus when a user selects a title I can then store in my
Clients table either the title text (ie Mr) or the Primary Key of the
Lookup table (ie 1).

I am trying to establish the best practice regarding
when you should store the PK and when the text.

Thanks Mark.

 

[SkipVought]

Feel it depends.

How likely is it that ALL the 'Mr.' titles would be changed to 'Herr', for instance?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dhookom]

I agree with Skip. I guess my lookup table in this instance would not have an autonumber primary key. These titles are so standard that using a number doesn't make sense to me.

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

I agree with Skip / Duane,

IMO 'Title' is a domain in DB theory and I would use real values not ID's in this instance.

You would still have your domain (look-up table) that could power a combo / drop down, as well as used for data typing / value constraints, only the table would consist of a single column containing the the actual title which is also the primary key.


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[Moss100]

This is interesting:

"You would still have your domain (look-up table) that could power a combo / drop down, as well as used for data typing / value constraints, only the table would consist of a single column containing the the actual title which is also the primary key."

I was watching some stuff on YouTube last night and this was the approach suggested there - ie. just having a table comprising of the value and setting this as the primary key.

On that basis, what is the logic used for determining when to use that approach as opposed to storing an ID number. I understand that using the ID number will be more efficient in terms of speed, but surely there is an argument for storing the actual value in the primary table.

Would it be the following be good practice:

If the value in the lookup table can make a suitable primary key, then store this value in the Primary table.
(i.e. Salutations such as Mr, Mrs or Porperty Types such as House, Flat etc)

If the Value does not, such as a Customer Name (which could have duplicates), then store the ID.


In addition to this, in lookup tables such as Saluations, would there typically be any need to set up a relationship between the lookup and the primary table?

Many thanks Mark






















case that in instances when the look up value would be suitable as a Primary Key

 

[MajP]

I think there is two types of issues here. The case where there is truly no related data only a choice, and the case where there may be some related data in the lookup. Keep in mind these are "lookup" tables and not "data" tables. Although I do not think there is a formal difference we are talking about tables with few fields and normally limited records. Some times they can be big like zipcodes, but you are adding, modifying, and deleting data very rarely. You add data once.


1) If it is truly just a choice, and no related information then for sure just store the value. In your example you have no additional related fields.
In fact I do this with actually 1 big table. I have two fields, choice and choiceType
Example:
Choice ChoiceType
Red Color
Blue Color
Green Color
Small Size
Medium Size
Large Size
Hot Temp
Warm Temp
Cold Temp

Now I can do a color, Size, and Temperature pulldowns.

2) But lets say I had some related data I want for my temperatures
tempName LoTemp HighTemp Symmbol
Hot 100 200 H
Warm 50 99 W
Cold 0 49 C

TempName makes a very good primary key. So I will use this as the PK. This makes things really easy

•Stable: does not change over time
•Minimal: fewest attributes necessary
•Factless: no hidden information
•Definitive: value always exists
•Accessible: available when data created
•Unique: absolutely no duplicates

3) But there is some art to it. If for example it was restaurant names instead of my simple temp name, I for sure would create an autonumber. Things with punctuation and spaces can cause some serious problems
Joe's Crab Shack
Mike's #1 Steak House

I shy away from even simple spaces so if my choices contain "Very Hot", "Ice cold" I have to think hard about the pros and cons.

4) The speed issue is really dealing with data tables and not lookups. If you are only storing a choice, it is surely faster to store and display Mr., Mrs. in your table than creating a link to a related table.

5) The big consideration is when you have a bad PK, but you think you will only use the value and never other related fields. So for example I have a list of restaurant names, and that is the only information I am going to store. I know that is a pretty bad PK, but I only think I will store the value so it should not matter. For ease of use I will just store the value. However, what always happens is somewhere down the line you think you need to group them by restaurant type so you need a related field. Now I have a bad PK which may cause problems. So now you would probably have to do some table redesign and data updates.

 

[Moss100]

Thank you very much for taking the time to run over that - it is very helpful.

So if I store tha actual value in the table, is it then OK to use lookups at table level, or are they just best avoided full stop at table level.

I'm asking that in relation to looking forward when I hope to upsize the back end tables to sql server. Do lookups at table level cause upsizing problems or is it simply that sql won't be able to use the lookups, but they won't cause any problems.

Thank you all kindly for your help. Mark.

 

[MajP]

So if I store the actual value in the table, is it then OK to use lookups at table level, or are they just best avoided full stop at table level

I would still avoid them. The confusion and problems they cause are just not worse it. You get all the benefits doing this at the form level or query level without any of the drawbacks. Yes you can do this in a query. IMO if MS was going to allow this in the table view, the fields should be identified so that you know that the table view is using a table lookup. I have spent hours trying to debug other people databases not knowing what is going on.

The only benefit of doing it at the table level is that when you create a form using a wizard it applies the same lookup and you do not have to recreate it. However you can still drop a combo on to a form and use the combobox wizard.

To me the biggest problem is why should I be encouraging user interface design at the table level. If I build a database I never let anyone enter data through a table, so why would I need pull downs in a table. If I want to add data behind the scenes then I would create a query to do that, and if I wanted I could add a pulldowns in the query.

So try this. Remove your lookups from the table. Make a query based on the table. On the field you want a pulldown right click and select Properties. Go to the lookup tab. Pick display type of combobox. Create your query for the pulldown choices. Now you do not have the pulldowns at the table view, but in a query based off the table view. This would now defeat the only benefit of doing it at the table. If you use the wizard to create a form from the query you get the pulldowns by default.

So why do you think you need them in the table view?

 

[Moss100]

Hi - Yes I was mainly using them to try and ensure that data integrity was maintained at table level.

I agree however that users should not be exposed to tables and so really this should not be a consideration.

I have removed the lookups from my tables and have modified the my 'basic' support tables (those where data is unlikely to change)so they no longer have an autonumber ID PK, but have the actual Value now set as the Primary Key.

This has made the lookup tables easier to look at and hopefully will remove any future confusion as to whether a value or number is being stored.

Im sure there will still be a few tables that cause me to wonder which way to go, but things seem a lot clearer.

Many many thanks to all.

 

[1DMF]

Hi Moss,

As MajP explains, domains are generally static values, that change rarely and can be used to enforce data integrity.

There are exceptions to every rule and it depends on your environment as to how you want to manage periodic changes.

EG.

I work in financial services. We have a provider table that stores the names of those who provide us products (I.E. Mortgage Lenders, Insurers), this could be a table that is just the name, however, it is used in many relationships, and some providers have stupidly long names, that may also change once in a blue moon. (Abbey National -> Santander) as just one example.

I have taken the decision to create the provider table with PK (ProviderID) of an auto identity insert integer type, with a separate text field for the actual name.

This makes the links between all the tales in the relationship easier to work with, as just numbers are involved, and at an edit level, I can alter the name in one table without any need to cascade changes to related tables.

I also have an 'active' column of Boolean value, so I can deactivate providers from appearing in drop down lists when they are removed from our panel, but remain in the system for historical referential integrity.

Also as I use the one table for all providers, there is a sub-typing in the CDM design to differentiate the type of provider (Insurer from Lender).

You also need to consider historical values when changes are implemented, if you cascade PK changes to all FK's, you may no longer have a record of what the value originally was, is this something you require?

When Abbey moved to Santander, I had to ask if the boss wanted old cases to reflect they were made under Abbey National, because altering the name in linked table would alter every case ever entered into the DB, or if historical records should remain Abbey National, then a new provider would need to be added to the system, but what about reporting, grouping would mean you have values for two different providers, when technically it's the same provider.... DB design and data management is never as easy as it may first seam, so careful analysis of your data is very important.

When you design your application, there is a process you go through that helps you decide what 'Entities' you need (tables), and whether there is additional information to be stored, or is it purely a data value type, such as 'Job Title' or 'Document Type', that is just a domain look-up value.

>> Im sure there will still be a few tables that cause me to wonder which way to go

Always!, And if you ever get stuck, you know where to ask ;-)




"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[Moss100]

Yes thank you for the time taken to give me such a detailed reply.

The assistance I have got here has really helped me get a grip with some of the
basics and I have spent this afternoon removing calculated fields and lookups from the primary tables and also making some of my lookup tables more simple.

All in all the projects feels a little more straightforward now.

At present I have created a lookup query for each lookup table. I did this so I could use these queries in combo boxes on forms etc. Of course I can use the combobox wizard to do the lookup so I was wondering how others approach this?

The downside of having a query for each lookup table is I'm ending up with a very long list of queries.

Thanks again - much appreciated. Mark.

 

[dhookom]

I expect 95% of my combo boxes on forms use SQL statements as Row Sources. I expect nearly the same percentage of forms and reports have SQL statements as Record Sources.

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

>> The downside of having a query for each lookup table is I'm ending up with a very long list of queries.

As you build your application, you'll end up with a long list of everything!

I am the same as Duane, queries for Row Sources, with a smidgen of hard-coded value lists or even dynamic population via VBA code.

All depends on what I'm using the combo box for



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[MajP]

The downside of having a query for each lookup table is I'm ending up with a very long list of queries.

As mentioned, I sometimes do this with one big table instead of a lot of little tables. If it is simply lookups without other related data

tblChoices

[tt]
Choice ChoiceType
 Red    Color
 Blue   Color
 Green  Color
 Small  Size
 Medium Size
 Large  Size
 Hot    Temp
 Warm   Temp
 Cold   Temp
[/tt]

So I could have a combo for colors like
Select Choice from tblChoices where choiceType = 'Color'
or have one for sizes
Select Choice from tblChoices where choiceType = 'size'

I also usually add a third field "SortOrder" in case you want to show a long list in a certain order.