Greetings again,

I have several fields into which I want only particular range-of-values. For example, I'll have a statesandprovinces field into which I'll only want the standard 2 character acronyms (MT for Montana, BC for British Columbia). So, the length will be 2, data type Alpha/Text, upper case (input mask, right?) and certain combinations are valid (range of values).

I have a similar table, fuels, into which I only want one of maybe 4 possible values.

I'm sure I have more cases like this, but can't think of others right off hand.

Should I build validation tables for each of these instances?

Reasons to create the table of values might be:

- to store at least one other column which is dependent on the value (the name of the state for example)
- to support adding or removing values at runtime
- to allow the set of values to be queried at runtime
- to make it possible to use a surrogate key in place of the business value

If none of these apply then you can consider using a CHECK constraint to validate the set of values rather than create a table for them.

So, in this case, the first condition doesn't really apply, since there is no reason for me to create attributes for any of the states.

However, the second does apply, if I understand the condition. A person inputting records and values (for example, inputting a value into employees.state) would be adding values at runtime, correct?

The third might be useful, for example if one wanted to know which employees lived in a certain state.

As far as the fourth, I'm not sure what operations I would run that would require a surrogate key... err, if I understand you correctly (surrogate key being a primary key internal to the database... correct?) since each acronym is unique. Can you think of any? (I'm not being coy, here. I'm trying to cement in my understanding.)

So, if I am understanding correctly, a validation table would be appropriate for this case. Would you agree?

not all, no

dave (dportas) makes several points, and i'll probably repeat most of what he said, but in different words

state/province codes are a great place to use a lookup table

even if you don't think you have any other data to store (in which case the table would consist of only one column -- the 2-char code), the table can be used to "vet" input values (dave's second point)

this is done by simply declaring the state code in the employee table as a foreign key

think again about the first point, though -- typically you might want to produce a report, say, of employees by state, and therefore it would shirley be nice if the report said Michigan, Mississippi, and Missouri, rather than MI, MS, and MO, so vwalah, there's your additional data that you want to store

the point about querying is that you'd use the lookup table to produce, for example, a dropdown list of states for an input form -- the alternative would be to do a SELECT DISTINCT statecode on the employees table, and even then, you'd get only those state codes where you had employees, so you'd probably end up hardcoding that list yourself anyway

the bit about using a surrogate key doesn't really apply here, as it would take a rare and esoteric set of circumstances for you to want to use a surrogate key instead of the very natural 2-character code

helps?

I'll throw in one further point, specifically regarding use of CHECK constraints. Personally, I hate using CHECK constraints for 'lists'
* The validation is now part of the schema - if the list changes you need to change the schema. That is more substantial than adding rows to a 'validation table'. As such, at the minimum you would want to be very, very sure that you list is not going to change.

I typically limit CHECK constraints to range or mathematical validations (for example, an integer must be positive; a DoB must be between x date and y date etc) rather than discrete values.

pootle flump ur codings are working excelent.

Actually I meant that you might want to add new state codes at runtime or remove old ones from the list of valid state codes. Validating inserts to another table can be done using a CHECK constraint - you don't always need a new table.

As Pootle says, using a CHECK constraint means you'll need a schema change to add or remove states and that isn't usually something you want to do at runtime. Whether using a CHECK constraint is a good idea or not is mostly down to the degree of change you expect. Having to do a schema change once a year shouldn't be a big deal but once a week probably is. Also it may be less convenient to record a history of schema changes whereas that's very easy to do with data in tables.

Strange to say, but none of my 6 intro books on db design mentions CHECK constraints. However, a couple of my SQL books do.

Could I get someone to forward me a link or two for reading on CHECK constraints? I've read a few entries (including w.pedia) but I'm still a bit fuzzy on the idea.

As I get it, it's a form of column definition. Is it conceptually similar to range of values settings? Edit: I suspect not, after reading this post:

"...check constraints do not override column definitions,
you cannot use a check constraint to prohibit null values if the column definition permits them..."

But, I think I'm getting the idea. The C. restraint forces the DMBS to compare values against the constraint during an input operation. However, I'm seeing references to the fact that these apply only to a record/row. I'm not sure how that would work. Can someone elucidate.... or again, send me a link to an article? End Edit.

And, please excuse the n00bness, but by schema do you mean the ERD? Or, would this include field specifications (I suppose it would if C. contraints are similar to ROV settings)?

Thanks for all the above. This process is definitely helping me along.

BTW, Is it true that "MySQL simply does not support CHECK constraints."

EDIT: I see that as I was editing this post, Rudy posted another reply, which I'll get to next. Thanks.

you have to combine two ideas:

first, the purpose, which is stated very nicely in the SQL Server docs (affectionately known as "BOL") as:domain integrity simply means the allowable values

if--and this is just a thought exercise, okay?--you had a table which was to store only integers which were cubes, your domain would be the cube numbers 1, 8, 27, 64, 125, etc.

your CHECK constraint would then have to ensure that every value you attempt to insert into this column meets that criterion

the second point is the the door is wide open as to how you accomplish this, because the syntax allows you to use queries (even if some database vendors don't implement this)

so sometimes it's important to check (heh) what your particular database system allows

It sounds to me like I need to stick with validation tables (same as "look-up"?) for now. I'm experimenting with my GIS database (Manifold) and with Access (since the forestry growth-and-yield software I use is based on Access)... to get a basic understanding and model. But, I'm not sure what DBMS I'll finally settle on. My preference is to stay as generic as possible for now.

Meanwhile, seeing the various paths I could take for this simple objective is most interesting. Lots of flexibility, depending upon one's priorities, considerations, and software limitations/abilities.

Avoid Access (by which I assume you mean a MDB/ACCDB database). Access is a fine application development tool but a truly lousy substitute for a real DBMS. You'll find it easier to be generic and portable by using almost anything else.

oh, pish posh

richard, access is an excellent tool which makes it easy to learn sql

all the red flags should be going up whenever you hear people knocking access

usually, there's a hidden agenda, or the barely concealed disdain of elitism

not that i'm accusing david of either, though, eh

I didn't think people used that expression (well not living people). Is it still used in the colonies?

thanks for the disdain, but you are right, i am only just still barely living

Is it ever used though? I'd be truly amazed if it was.

Oh piffle, I hear it all the time!