one SET vs. many BIT (TINYINT)? pros/cons?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Terry Parker
    #1

    one SET vs. many BIT (TINYINT)? pros/cons?

    I have a database that stores text (and other objects) which I would like to
    categorize under multiple categories. I started out creating a "SET" field
    with the different category names and I imagined being able to retrieve them
    via matching on one or more categories and having this field indexed.

    Or, I could also add a BIT (which I understand right now is the same as
    TINYINT) field for each category and then assign 0 to rows that don't fit
    that category and 1's to the categories that do fit. I would then query the
    field names/categories I was looking for via the field name and a value of
    1.

    It seems like one downside is that to optimally do this I would then also
    have to index each of these BIT fields for fast retrieval/search/match.

    I'm currently looking at about 15-20 categories, but I could see it going up
    to 30-40, very unlikely beyond the 64 limitation of the SET field.

    However, I wanted to toss this out and get pro/cons I might be missing,
    especially as use of the SET field is new to me and I don't want to start
    down that path and find out it was a bad way to go. TIA,

    Terry
    terryRemoveThis parkeratmyselfd otcom


    Tags: None
    • Justin C. Le Grice
      #2
      Re: one SET vs. many BIT (TINYINT)? pros/cons?

      Terry Parker wrote:
      [color=blue]
      > I have a database that stores text (and other objects) which I would like to
      > categorize under multiple categories. I started out creating a "SET" field
      > with the different category names and I imagined being able to retrieve them
      > via matching on one or more categories and having this field indexed.
      >
      > Or, I could also add a BIT (which I understand right now is the same as
      > TINYINT) field for each category and then assign 0 to rows that don't fit
      > that category and 1's to the categories that do fit. I would then query the
      > field names/categories I was looking for via the field name and a value of
      > 1.
      >
      > It seems like one downside is that to optimally do this I would then also
      > have to index each of these BIT fields for fast retrieval/search/match.
      >
      > I'm currently looking at about 15-20 categories, but I could see it going up
      > to 30-40, very unlikely beyond the 64 limitation of the SET field.
      >
      > However, I wanted to toss this out and get pro/cons I might be missing,
      > especially as use of the SET field is new to me and I don't want to start
      > down that path and find out it was a bad way to go. TIA,
      >
      > Terry
      > terryRemoveThis parkeratmyselfd otcom
      >
      >[/color]

      You is going down the hard way.

      Try this.

      Create a table with CategoryID and CategoryName

      Create another table with LinkID, MainRecordID, CategoryID and index
      away on this table

      Now you have a flexible system in that if you add categories you don't
      have to disturb the table structure because your categories are just a
      record in a table.

      I personally use this a lot and find it easier than adding new fields or
      messing with decoding strings etc.

      Hope this helps

      Regards

      Justin

        Comment

        • Justin C. Le Grice
          #3
          Re: one SET vs. many BIT (TINYINT)? pros/cons?

          Terry Parker wrote:
          [color=blue]
          > I have a database that stores text (and other objects) which I would like to
          > categorize under multiple categories. I started out creating a "SET" field
          > with the different category names and I imagined being able to retrieve them
          > via matching on one or more categories and having this field indexed.
          >
          > Or, I could also add a BIT (which I understand right now is the same as
          > TINYINT) field for each category and then assign 0 to rows that don't fit
          > that category and 1's to the categories that do fit. I would then query the
          > field names/categories I was looking for via the field name and a value of
          > 1.
          >
          > It seems like one downside is that to optimally do this I would then also
          > have to index each of these BIT fields for fast retrieval/search/match.
          >
          > I'm currently looking at about 15-20 categories, but I could see it going up
          > to 30-40, very unlikely beyond the 64 limitation of the SET field.
          >
          > However, I wanted to toss this out and get pro/cons I might be missing,
          > especially as use of the SET field is new to me and I don't want to start
          > down that path and find out it was a bad way to go. TIA,
          >
          > Terry
          > terryRemoveThis parkeratmyselfd otcom
          >
          >[/color]

          You is going down the hard way.

          Try this.

          Create a table with CategoryID and CategoryName

          Create another table with LinkID, MainRecordID, CategoryID and index
          away on this table

          Now you have a flexible system in that if you add categories you don't
          have to disturb the table structure because your categories are just a
          record in a table.

          I personally use this a lot and find it easier than adding new fields or
          messing with decoding strings etc.

          Hope this helps

          Regards

          Justin

            Comment

            • Justin C. Le Grice
              #4
              Re: one SET vs. many BIT (TINYINT)? pros/cons?

              Terry Parker wrote:
              [color=blue]
              > I have a database that stores text (and other objects) which I would like to
              > categorize under multiple categories. I started out creating a "SET" field
              > with the different category names and I imagined being able to retrieve them
              > via matching on one or more categories and having this field indexed.
              >
              > Or, I could also add a BIT (which I understand right now is the same as
              > TINYINT) field for each category and then assign 0 to rows that don't fit
              > that category and 1's to the categories that do fit. I would then query the
              > field names/categories I was looking for via the field name and a value of
              > 1.
              >
              > It seems like one downside is that to optimally do this I would then also
              > have to index each of these BIT fields for fast retrieval/search/match.
              >
              > I'm currently looking at about 15-20 categories, but I could see it going up
              > to 30-40, very unlikely beyond the 64 limitation of the SET field.
              >
              > However, I wanted to toss this out and get pro/cons I might be missing,
              > especially as use of the SET field is new to me and I don't want to start
              > down that path and find out it was a bad way to go. TIA,
              >
              > Terry
              > terryRemoveThis parkeratmyselfd otcom
              >
              >[/color]

              You is going down the hard way.

              Try this.

              Create a table with CategoryID and CategoryName

              Create another table with LinkID, MainRecordID, CategoryID and index
              away on this table

              Now you have a flexible system in that if you add categories you don't
              have to disturb the table structure because your categories are just a
              record in a table.

              I personally use this a lot and find it easier than adding new fields or
              messing with decoding strings etc.

              Hope this helps

              Regards

              Justin

                Comment

                Previous template Next
                Working...