Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2008
    Posts
    28

    Is a yes_no table a bad idea?

    So... I'm tinkering around with a contacts database, and after reading various bits n pieces I've gotten the notion that it might be better to have a very small, one column, two value table like this:

    Code:
    CREATE TABLE yes_no(
    	value CHAR(3) NOT NULL PRIMARY KEY
    ) ENGINE=InnoDB;
    for use as a foreign key for things like say, a phone number or email address table where people might have a home number, a work number, a mobile number, etc. but one person may want their home number to be the 'primary' contact number, and another may want their mobile number to the be primary contact. I thought adding a column 'is_primary' as a fk that references that simple table might be an alternative to using enumerate. Similarly, for other projects I have in mind it might be possible to have records for people that are kept for historical purposes, but I'd need some way to differentiate between 'active' records and those that are not - seemed like an 'is_active' column referencing the 'yes_no' table would do the job.

    Why not ENUM for something like this where the values (yes & no) are likely to never change? I guess my understanding of ENUM is that it is a MySQL thing and may not work the same on PostgreSQL or SQLite. I just don't want to try to do the Right Thing but end up borrowing trouble because of using such a small lookup table.

    TIA,

    Monte
    Last edited by memilanuk; 02-16-12 at 21:21.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    I don't think either an enumerated type or a lookup table is appropriate for what is essentially a boolean value.

  3. #3
    Join Date
    Nov 2008
    Posts
    28
    Okay... and your best suggestion would be...?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    I don't know if it's the best, but, depending on whether the DBMS in question supports the boolean datatype, I'd define IS_PRIMARY as either BOOLEAN or SMALLINT. CHAR(1) is also an option.

  5. #5
    Join Date
    Nov 2008
    Posts
    28
    Hmmm... so it looks like MySQL doesn't support BOOLEAN, but suggests TINYINT (again, proprietary to MySQL) or CHAR. PostgreSQL supports BOOLEAN (another +1 for postgres!) and CHAR. SQLite looks like it doesn't directly support BOOLEAN but does store them as integers as '0' or '1'.

    Doesn't really appear to be one type (besides CHAR or just INT) that would work across the different DB types, and then I'd have to incorporate some logic into the application just for interpreting what the '0' or '1' translated to.

    Getting back to MySQL, since I have been tinkering with it on Portable XAMPP a bit... if using some form of INT or CHAR... how would you ensure that '0' or '1' (or 'T'/'F', 'Y'/'N', 'M'/'F', etc.) was the only thing that could be put in that field? Would you end up having to rely on the application logic since MySQL doesn't respect check constraints and doesn't have a BOOLEAN type?

    Edited to add:

    Looks like I was partially mistaken about MySQL and BOOLEAN... it doesn't support it as a true BOOLEAN datatype, but does support it as synonymous for TINYINT(1), and 'true'/'false' are aliases for '1' and '0'.
    Last edited by memilanuk; 02-17-12 at 18:00.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    According to the MySQL site:
    BOOL, BOOLEAN
    These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true
    We intend to implement full boolean type handling, in accordance with standard SQL, in a future MySQL release.
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Because a value with only two states (true and false) won't index very well in most cases (you want the index value to be selective in order for the index to be effective), there isn't much purpose in using a foreign key.

    The only reason I would use a lookup table for a binary value might be to allow multiple language support. It won't help the database engine to build queries unless much of your processing depends on the value of a certain column and a very small percentage of your rows have one value or the other.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Nov 2008
    Posts
    28
    Thanks for the input, folks. For the time being I went with 'BOOLEAN' for the column type which upon later inspection with 'SHOW CREATE TABLE' is listed as 'TINYINT(1)'.

    This is one of those areas that it just puzzles me that the various databases would have so much variance over something so basic and seemingly cut-n-dried.

Posting Permissions

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