Help with table structure and forming the Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Help with table structure and forming the Query

Author

Topic

naez
Starting Member

1 Post

Posted - 2008-10-14 : 22:33:23

Okay so I've got a couple tables:

articles
-----------
id - primary
cat_id - INT index foreign #links up with categories table
author_id - INT index foreign # links up with user table 
title - varchar(255)
body - longtext
url - varchar(255)  # url-safe-tutorial-title-for-seo-links
-----------

categories
-----------
id - primary
category - varchar(255)
-----------

The only thing is, I want to seperate things off by level too.

So for instance:

"Advanced" articles will carry say category id's 3,4,5
"Intermediate" will carry 2
"Novice" will carry cat_id 1

with room for expansion of course.

Now I know a couple ways to do this... add a new table, or add a parent_id field in categories. But I'm not sure the best way to do this so hralp!

And more importantly, how to generate a big-ass query that will do all the following:


Novice:
 - Novice Category 1 (2 articles)
Intermediate
 - Intermediate Category 1 (5 articles)
 - Intermediate Category 2 (2 articles)
 - Intermediate Category 3 (20 articles)
Advanced:
 - Advanced Category 1 (2 articles)

# I know I'll need some semi-intricate joins and groupings, and a COUNT()

I'm not exactly the SQL JOINs master so hopefully I can get some hralp.

Also on side note should I use longtext or something else for the body?

Thanks!

jose_piratilla
Starting Member

7 Posts

Posted - 2008-10-15 : 01:04:40

I don't like to use meaningful values on the ID field. Do you plan to change your categories tree?

If not I would use something like this

categories
----------
id -> Only ID number, no meaning.
CatNumber -> 1,2,3
Level -> "Advanced", "Intermediate", "Novice"

The SQL Query would be something like

SELECT categories.Level, " - " & categories.Level & " Category " & Trim(Str(categorties.CatNumber)) AS CatName, COUNT(articles.Id) As ArtNum
FROM categories JOIN articles ON categories.id=articles.id_cat
GROUP BY categories.id, categories.Level, categories.CatNumber
ORDER BY categories.level DESC, CatName;

But if the categories can be changed I would use

categories
----------
id -> Only ID number, no meaning.
CatNumber -> 1,2,3
id_level

levels
------
id
LevelName
LevelOrder

And the query would be:

SELECT levels.LevelName, " - " & levels.LevelName & " Category " & Trim(Str(categorties.CatNumber)) AS CatName, COUNT(articles.Id) As ArtNum
FROM (categories JOIN levels ON categories.id_level = levels.id) JOIN articles ON categories.id=articles.id_cat
GROUP BY categories.id, levels.LevelName, categories.CatNumber, levels.LevelOrder
ORDER BY levels.LevelOrder, CatName;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources