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

[Terry Parker]

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
terryRemoveThisparkeratmyselfdotcom

[Justin C. Le Grice]

Terry Parker wrote:

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
terryRemoveThisparkeratmyselfdotcom

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

[Justin C. Le Grice]

Terry Parker wrote:

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
terryRemoveThisparkeratmyselfdotcom

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

[Justin C. Le Grice]

Terry Parker wrote:

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
terryRemoveThisparkeratmyselfdotcom

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