organizing the tables...

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
hi! i'm trying to builid web catalog and i'm having problems with organizing
my tables. here's what i want:

i have main categories, sub_categories and articles like this:


and this is my idea how to do this:
1) first there's table CATEGORIES with "id" and "cat_name"
2) then there's table SUB_CATEGORIES with "id" and "subcat_name"
3) then table ARTICLES with "id", "subcat_id", "article_name" and
4) finally there's table with relations between categories and
                    id    category    subcategory
                    1        1                    1
                    2        1                    2
                    3        2                    3
                    4        2                    4
                    5        2                    5

how i read results:
1) choose category : script outputs category name
2) sql query gets all sub_categories for selected category from table
        3) output sub_category name
        4) select all articles and prices for selected sub_category from
table "Articles"
        5) output atricles and prices
    till there's no sub_categories left

(sorry for long post)

my question is:
    - is there any more elegant and faster way to do this?

thank you all!

Re: organizing the tables...

ToMeK wrote:
Quoted text here. Click to load it

Number 4, I think, is completely unnecessary.  Instead, consider
adding a `cat_id` field to the `SUB_CATEGORIES` table.

Also, be sure the following fields are indexed:
SUB_CATEGORIES.cat_id (if you choose to have it, that is)


Re: organizing the tables...

maybe this helps.  You're trying to embed what are really arbitrary
indexes into the structure of your tables containing your data. Why
can't you have a flat table of articles with IDs,  and then build
category indexes independently on other tables,  whatever structure
they may take in the future.

Articles on futility of modeling:

Try to avoid foreign keys in the tables containing the basic data


Re: organizing the tables...

I noticed that Message-ID: contained the following:

Quoted text here. Click to load it
Quoted text here. Click to load it

Absolutely.  An entity relationship diagram would help.  To determine
the required number of tables the OP needs to know the relationship
between the entities.  For instance one sub category can contain many
articles but can one article appear in many sub categories?  If the
answer is yes, the OP has a many to many relationship and a link table
is required.  

Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs /

Re: organizing the tables...

I assumed that an article can appear in many categories.  But I'd go as
far as creating a link table for every relation,  even if one-to-one.
I really don't like foreign keys in data tables.   ER diagrams are nice
in the abstract,  but might lead to more complexity,  or make you think
you have an inheritance hierarchy or network or small world or
whatever,  when you really only want two layers of tables.  The upper
layer is a straight line of tables representing relations,  groups,
structures,  categories,  indexes, inheritance, what have you,  the
bottom layer is a straight line of data tables without foreign keys.

Site Timeline