Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Feb 2009
    Posts
    104

    when to use validation tables

    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?

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    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.

  3. #3
    Join Date
    Feb 2009
    Posts
    104
    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?

    Quote Originally Posted by dportas
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rbfree
    Should I build validation tables for each of these instances?
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by rbfree
    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?
    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.

  7. #7
    Join Date
    Feb 2009
    Posts
    104

    reading on CHECK constraints

    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.
    Last edited by rbfree; 02-12-09 at 13:34.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you have to combine two ideas:

    first, the purpose, which is stated very nicely in the SQL Server docs (affectionately known as "BOL") as:
    CHECK constraints enforce domain integrity by limiting the values that are accepted by a column
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2009
    Posts
    104

    generic approach

    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.

  10. #10
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by rbfree
    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.
    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.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dportas
    ...a truly lousy substitute for a real DBMS.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by r937
    oh,pish posh
    I didn't think people used that expression (well not living people). Is it still used in the colonies?

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks for the disdain, but you are right, i am only just still barely living
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Is it ever used though? I'd be truly amazed if it was.

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Oh piffle, I hear it all the time!
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •