How to check for sure that a table is a validation table?

[magnusstefan]

"You have a lot of comments there."
Yes I know, and I try to answer them to the best of my ability.

"I see a couple of questions, too."
I know, I did send in them on purpose.

"If I can add anything to help ..."
I think you can.

"In my databases I will prefix table names with tbl. I will also prefix table names with lst. If a table will behave like a list, for example a list of states, the table would be named something like 'lstStates'. Notice how I did not use the word 'lookup'?"
I understand what you say, and what you do. But why do you do it? I mean, it has long ago been established several basic design practices which are explained in every beginners book and also on a lot of other places. And one of the things they always say is that, whatever you call it, "lists", "validation tables" or something else, is a quite normal thing to include in a database. And if you look at, say twenty MS Access example databases taken from Microsoft's own production, or made by the authors of the design books, perhaps fifty percent of them actually also use validation tables.

And, now and then, you can also clearly see this in the diagrams in the books, or written directly in the property field of the table like: "Validation table for...", etc. Can you please explain to me what you think is the problem with calling validation tables for what it is, rather than calling them a "list" and give a hint in the table name, starting it with "lst..."?

"Where do you draw the line?"
When there are two choices or more of anything that must be validated, because it will later be used in a form or report, and then the choices must be restricted to certain values in a certain field in a certain table, due to the agreed business rules for that very database.

"It is just semantics to me. Call it what you want"
If it is just semantics to you, why cannot you then just call them "validation tables", why do you need to avoid that word, and instead use "list" which is functionally the same thing as you describe it?



"If you want to understand the database, understand the relations."
That is exactly what I am trying to do.

"If you want to understand an application, understand the Business Rules."
I agree. But if the business rules not are documented, then you cannot understand them in any other way than through reverse engineering, and of course speak with people at the actual place if someone happens to be able to explain.

" If you want to make it more complicated than that..."
I never have a conscious intention for making anything more complex than it already is.

[magnusstefan]

Fine!

This answer was really important, since it helped me to better understanding of a certain defined aspect of the confusion around the usage of the word "look up".
And an important aspect too!

The link was very important too, for three reasons:
1) It confirmed that what I already learnt from several design books. Not that I did not trust the authorts, for instance. M J Hernandez in "Database design for mere mortals". http://www.informit.com/store/databa...-9780133122299
but it just felt good to see it in another perspective: More taken from actual reality.

2) The other reason was this passage: "Most database environments have one lookup table to support each domain—a simple, scalable model. But recently I've read some articles proposing a new approach to lookup tables. Instead of one lookup table to support each domain, some database designers are advancing the idea of a single large lookup table that contains all valid values for every domain." I have seen this discussion on other places, and I was already then most perplexed: Why would anyone to such a silly thing as ruin the table design, and the same time the possibility for anyone else than the designer of the database to understand it. (With "silly thing" I mean put all validation for all data tables in one validation table.)

3) It not even suggested to NOT use validation tables, the author obviously takes it for granted that, if you need them, then you use them, and that normally you would need them!

In short: I agree with the writer!

[magnusstefan]

magnusstefan,

If you want to post a copy of the database, I would look at it and make comments/suggestions.

(I cannot upload the databases given in the examples below, since they are to big!)

Fine! I take two examples, one from the well known "Northwind" the example database from Microsoft, and the other ,
taken form "Sales order", which is an example database from the book
"SQL Queries for mere mortals" .

The Example in Northwind:
1) (For some reason, I cannot find an English version, but I will translate the field and table names when necessary):
If you look at the "Order" (Same in Swedish) in design view (Table is called "Tabeller" in Swedish), and go to field name (Fältnamn) "Speditörs-ID" (Shipping agent ID), and look at the tab lookup (uppslag) you will see this as row source (radkälla) "SELECT [ID], [Företag] FROM [Speditörer (utökat)] ORDER BY [Företag];" ("SELECT [ID], [Company] FROM [ShippingAgents (expanded)] ORDER BY [Company];"

That is very clear: In any post the table order, the value in the field "Speditörs-ID (Shipping agent ID) is restricted to the values present in the field "ID" in the table "Speditörer" (Shipping agents). So the posts are validated with the help of a table which is a validating table.

Strangely enough, in this example it is obvious what is going on if you examine the database, but there is no inside documentation! The properties fields are totally empty in all tables, and all fields!

2) The other example, taken form "Sales order", is the other way round:
The table "Products" are in the properties described as "Information about products we sell" so we understand that it is a data table, and the table "Categories" is described in the properties as "Product category lookup table". Cannot be clearer?

But when you look in the table Products in design view, no lookups are used!

So in the first example, the tables are undocumented, but we can examine them and see which are data tables, and which are lookup tables, and in the other case we are clearly informed that a certain table is a lookup table, but that is just a definition, it is not used as that. Not other than perhaps in some SLQ queries.

So what is my question?
Just this: In both examples we clearly see lookup tables. Either in use, or in potential use, and that's it. Or?

I mean: How was it now, do I really need to examine a database further than in the two examples to understand if there are look up tables present or not, or is there also, as has been suggested, anything more to check? For instance some VBA code? And if so, what to look for in it? I mean, even if you have very low skills in VBA, if any validation valued are incorporated in the code, you should be able to see them somewhere inside an instruction as for instance ..."low, medium, high"...

Right?

[orange]

A couple of comments:

Don't use ID as the field name. If you're describing a Company table, call the field CompanyID. If an Order table, call the field OrderID. Make all names meaningful- not just for you, but for anyone coming after you. The ID only approach can end up with queries with several tables all referencing ID(confusing).

You can see lots of table examples in the data models at Barry Williams' site.

I think you are over thinking the lookup as validation.

A lot of validation is done in the Form Before Update event ---the last place to make a change before a record is saved.

The Select Case construct see techonthenet is often used with validation also.

Good luck

[magnusstefan]

A couple of comments:

Don't use ID as the field name. If you're describing a Company table, call the field CompanyID. If an Order table, call the field OrderID. Make all names meaningful- not just for you, but for anyone coming after you. The ID only approach can end up with queries with several tables all referencing ID(confusing).

Yes, I know that very well, this is a misunderstanding, I am not discussing how the field names should be designed. But in this case I understand that you thought I needed some information about it, but actually I just used the field names wrongly designed by Microsoft! You see, for some reason, the team (?!) designing the Northwind database created this design fault only in the table "Speditörer" (Shipping Agents). All other tables I checked has correct naming standards for the ID-fields, like "Order-ID, etc!

You can see lots of table examples in the data models at Barry Williams' site.

A very good site! Thanks for the information!

I think you are over thinking the lookup as validation.

And I do not. =)

A lot of validation is done in the Form Before Update event ---the last place to make a change before a record is saved..

Yes, I am sure they are! But in the two examples in the link the validation are checking that the entered date is reasonable, and that a record has not been chosen twice. What I ask for is code example which is used for the exact same purpose as in a validation table, for instance that the only possible values, for instance: "Customercountry" in the table "Customer" should be "USA", "Canada" or "Mexico" since a certain Company only deliver to customers in those Three countries.

[/QUOTE]
The Select Case construct see techonthenet is often used with validation also. [/QUOTE]
I do not know enough code to be able to understand if this is what I am asking about, but perhaps it is, and could be compared to my example above? I so my example could be translated to something like:
Select Case LNumber
Case 1 To 10
[CountryName] = "USA"
Case 11 To 20
[CountryName] = "Canade"
Case 21 To 30
[RegionName] = "Mexico"
End Select

But I got a feeling, that it is not so...

"Good luck"
I sure need that!

[orange]

I do not know enough code to be able to understand if this is what I am asking about, but perhaps it is, and could be compared to my example above? I so my example could be translated to something like:
Select Case LNumber
Case 1 To 10
[CountryName] = "USA"
Case 11 To 20
[CountryName] = "Canade"
Case 21 To 30
[RegionName] = "Mexico"
End Select

But I got a feeling, that it is not so...

Suppose you had a variable LNumber (integer) that represented a Location code of CountyExportedTo

where 1 represents USA, 2 Canada, and 3 Mexico.

Then in the validation...

...
Select Case LNumber
     Case 1  
        [CountryName] = "USA"
     Case   2 
        [CountryName] = "Canada"
     Case 3 
        [CountryName] = "Mexico"
  Case Else
  MsgBox "Undefined LNumber  " & LNumber
End Select

I don't see the need for 1 to 10 to represent USA, 11-20 for Canada. The field involved -CountryName - would be common to all valid values.

[ItsMe]

@magnusstefan

I understand what you say, and what you do. But why do you do it? I mean, it has long ago been established ...

Because I prefer to generalize it and describe it as a table. If, at the end of the day, a specific table looks, walks, and talks like a duck, by all means, call it a duck. In an Access desktop database, it is not very likely one of your ducks will require special design principals. The exceptions for any of my databases would be a table with an lst prefix and or using enumerations in VBA. If you are working with a team, you will need to do it a certain way. If I have the option, I am going to keep it as simple as possible. After all, business rules trump EVERYTHING and a designer/developer should put the User first.

[magnusstefan]

@magnusstefan

Because I prefer to generalize it and describe it as a table. If, at the end of the day, a specific table looks, walks, and talks like a duck, by all means, call it a duck. In an Access desktop database, it is not very likely one of your ducks will require special design principals. The exceptions for any of my databases would be a table with an lst prefix and or using enumerations in VBA. If you are working with a team, you will need to do it a certain way. If I have the option, I am going to keep it as simple as possible. After all, business rules trump EVERYTHING and a designer/developer should put the User first.

I am not really sure that I understand you all the way, but that does not matter now! The discussion is "complete enough" for me in this subject, at this point, so we can leave it there, and thank you for your participation!

[magnusstefan]

OK!

I think we both understand that there is no point going further on in this direction in the discussion until I actually have a basic grasp of VBA. So we can stop here for now, since I think I have as Close to an answer of the original questions, as it is possible to reach for me at the moment! And thank you for your participation!