Database Design Question - Press Release Categories? 1

[MrPink1138]

I'm building a database and some ASP pages to serve press releases for a website. I'd like to categorize each release so, for example, I can have a main page that lists them all... and on specific pages throughout the site I can pull out relevant press releases, ie: on my pages about apples i can pull out only the press releases that relate to apples... on the oranges page, only the ones that relate to oranges, etc, etc.

It is important that a press release can be flagged for multiple categories

I have about 40 categories. My question is what would be the most optimized way to do this in SQL2000? I know I can add bit fields for each category and use WHERE apple=1 but would there be any performance issues with having 40 bit fields in my table?

I've also thought about having a varchar filed called categories and storing a 40 character string like 0001010001010000001010000... which would store all the flags for the categories and then parsing it in my ASP code or with some fancy SQL.

Does anyone have any opinions on which method would be most efficient? Any information would be appreciated.
Thanks,
J

 

[vongrunt]

Both method are almost equally bad . The only advantage they give is somewhat easier client/app code for editing/maintenance.

One release may have multiple categories, therefore make another table (releaseID, categoryID).

 

[johnherman]

Relational Database theory would agree with vongrunt, make a bridge/associative/relation table between the two table with the many to many relationship, however....

For performance reasons, you may want to test both the relational method and the "bucket" method (where you have a flag for each method).

One critical question which could tilt the balance. How often do you add categories? Naturally, adding categories in the bucket/flag scenario is messy. Much easier with the bridge table.

HTH

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[johnherman]

Oops, forgot to mention. I think the lower performance will be when you have the bridge/relation table as it requires an additional join versus having them all in one record with the flag/bucket approach.

Another factor which will affect performance is the number of categories for each press release. If the average number of categories is large, the bucket approach will be much better. If there are only a few categories per release, it will not be as signficant. You may want to calculate an average number of categories per press release for additional guidance to the proper solution.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[vongrunt]

AFAIK the major problem with relational by-the-book approach is that it makes trivial operations (get a single release, update single release, list all releases w/ assigned categories) a bit more complex. Bucket method on the other hand requires more storage space (probably not a big deal), cannot use indexes efficiently and leads to bunch of dynamic SQL statements. At the very end, I guess choice depends on amount of data, expected types of queries and desired level of data integrity.

Between 40 bits and char(40), I'm not sure. Some arguments:

40 bits:
- more readable
- cannot be used for GROUP BY. OK, make it char(1)
- column stats are next-to-useless
- for new category you have to alter table

char(40)
- less readable queries (substring() & stuff)
- takes extra preparations for edit/updates
- easier to add new category
- for narrow result sets, perhaps forced index scan can be faster