469,090 Members | 1,094 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,090 developers. It's quick & easy.

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
terryRemoveThisparkeratmyselfdotcom
Jul 20 '05 #1
3 1855
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
Jul 20 '05 #2
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
Jul 20 '05 #3
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
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

13 posts views Thread by Axehelm | last post: by
1 post views Thread by Ronnie Patton | last post: by
reply views Thread by Sniffle | last post: by
5 posts views Thread by Fred | last post: by
3 posts views Thread by Andrea | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.