organizing the tables...

[ToMeK]

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:

CATEGORY_1
SUB_CAT_1
Article11
Article12
(...)
SUB_CAT_2
Article24
Article25
(...)
(...)
CATEGORY_2
SUB_CAT_3
Article35
(...)
(...)
(...)

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_nam e"
3) then table ARTICLES with "id", "subcat_id" , "article_na me" and
"art_price"
4) finally there's table with relations between categories and
sub_categories
like:
relations
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
"relations"
loop
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!

[David Gillen]

Re: organizing the tables...

Personally I'd store a Category ID In the Sub-cat table,
AND a sub-cat id in teh article table. You could probably speed things up by
storing a CatID in the article table too. But it may be overkill. REally
depends on how much pressure the DB server is under. Make sure to set the all
up as indexes.

db

An noise sounding like ToMeK said:[color=blue]
> 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:
>
> CATEGORY_1
> SUB_CAT_1
> Article11
> Article12
> (...)
> SUB_CAT_2
> Article24
> Article25
> (...)
> (...)
> CATEGORY_2
> SUB_CAT_3
> Article35
> (...)
> (...)
> (...)
>
> 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_nam e"
> 3) then table ARTICLES with "id", "subcat_id" , "article_na me" and
> "art_price"
> 4) finally there's table with relations between categories and
> sub_categories
> like:
> relations
> 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
> "relations"
> loop
> 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!
>
>[/color]


--

/(bb|[^b]{2})/
Trees with square roots don't have very natural logs.

[ToMeK]

Re: organizing the tables...

thanks for reply, first to say i'm very new to databases and php, so i'm
having slight problems following your reply.
what do you mean by:
"Make sure to set the all up as indexes"

tm

"David Gillen" <Belial@RedBric k.DCU.IE> wrote in message
news:slrnd0hcg4 .5t2.Belial@car bon.redbrick.dc u.ie...[color=blue]
> Personally I'd store a Category ID In the Sub-cat table,
> AND a sub-cat id in teh article table. You could probably speed things up[/color]
by[color=blue]
> storing a CatID in the article table too. But it may be overkill. REally
> depends on how much pressure the DB server is under. Make sure to set the[/color]
all[color=blue]
> up as indexes.
>
> db
>
> An noise sounding like ToMeK said:[color=green]
> > hi! i'm trying to builid web catalog and i'm having problems with[/color][/color]
organizing[color=blue][color=green]
> > my tables. here's what i want:
> >
> > i have main categories, sub_categories and articles like this:
> >
> > CATEGORY_1
> > SUB_CAT_1
> > Article11
> > Article12
> > (...)
> > SUB_CAT_2
> > Article24
> > Article25
> > (...)
> > (...)
> > CATEGORY_2
> > SUB_CAT_3
> > Article35
> > (...)
> > (...)
> > (...)
> >
> > 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_nam e"
> > 3) then table ARTICLES with "id", "subcat_id" , "article_na me" and
> > "art_price"
> > 4) finally there's table with relations between categories and
> > sub_categories
> > like:
> > relations
> > 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
> > "relations"
> > loop
> > 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!
> >
> >[/color]
>
>
> --
>
> /(bb|[^b]{2})/
> Trees with square roots don't have very natural logs.
>[/color]

[David Gillen]

Re: organizing the tables...

An noise sounding like ToMeK said:[color=blue]
> thanks for reply, first to say i'm very new to databases and php, so i'm
> having slight problems following your reply.
> what do you mean by:
> "Make sure to set the all up as indexes"
>[/color]
You've a primary key in your tables, That is an index.
SELECT * FROM BLAH WHERE <PRIMARY_KEY> = 'whatever'
Queries against the primary key will go very quickly, because it is an index.
Similar, if you know you're going to be running lots of queries against
another field which isn't your primary key when doing the create table call
you add in index (<field_name>) near the end. Check the sql reference for you
database and it'll tell you exactly the syntax to use. It'll just speed things
up. Of course, if you set up every field as an index it won't really improve
things. As there is some extra overhead with inserts and updates.

Hope that helps.

D.[color=blue]
>
> "David Gillen" <Belial@RedBric k.DCU.IE> wrote in message
> news:slrnd0hcg4 .5t2.Belial@car bon.redbrick.dc u.ie...[color=green]
>> Personally I'd store a Category ID In the Sub-cat table,
>> AND a sub-cat id in teh article table. You could probably speed things up[/color]
> by[color=green]
>> storing a CatID in the article table too. But it may be overkill. REally
>> depends on how much pressure the DB server is under. Make sure to set the[/color]
> all[color=green]
>> up as indexes.
>>
>> db
>>
>> An noise sounding like ToMeK said:[color=darkred]
>> > hi! i'm trying to builid web catalog and i'm having problems with[/color][/color]
> organizing[color=green][color=darkred]
>> > my tables. here's what i want:
>> >
>> > i have main categories, sub_categories and articles like this:
>> >
>> > CATEGORY_1
>> > SUB_CAT_1
>> > Article11
>> > Article12
>> > (...)
>> > SUB_CAT_2
>> > Article24
>> > Article25
>> > (...)
>> > (...)
>> > CATEGORY_2
>> > SUB_CAT_3
>> > Article35
>> > (...)
>> > (...)
>> > (...)
>> >
>> > 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_nam e"
>> > 3) then table ARTICLES with "id", "subcat_id" , "article_na me" and
>> > "art_price"
>> > 4) finally there's table with relations between categories and
>> > sub_categories
>> > like:
>> > relations
>> > 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
>> > "relations"
>> > loop
>> > 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!
>> >
>> >[/color]
>>
>>
>> --
>>
>> /(bb|[^b]{2})/
>> Trees with square roots don't have very natural logs.
>>[/color]
>
>[/color]


--

/(bb|[^b]{2})/
Trees with square roots don't have very natural logs.

[NC]

Re: organizing the tables...

ToMeK wrote:[color=blue]
>
> 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:
>
> CATEGORY_1
> SUB_CAT_1
> Article11
> Article12
> (...)
> SUB_CAT_2
> Article24
> Article25
> (...)
> (...)
> CATEGORY_2
> SUB_CAT_3
> Article35
> (...)
> (...)
> (...)
>
> 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_nam e"
> 3) then table ARTICLES with "id", "subcat_id" , "article_na me" and
> "art_price"
> 4) finally there's table with relations between categories and
> sub_categories[/color]

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:

CATEGORIES.id
SUB_CATEGORIES. id
SUB_CATEGORIES. cat_id (if you choose to have it, that is)
ARTICLES.id
ARTICLES.subcat _id

Cheers,
NC

[gumshoo3@yahoo.com]

Re: organizing the tables...

Hi,
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:

http://www.bkent.net/Doc/limrec.htm

http://www.bkent.net/Doc/brinmod.htm

Try to avoid foreign keys in the tables containing the basic data
(entities).

HTH

[Geoff Berrow]

Re: organizing the tables...

I noticed that Message-ID:
<1107883979.193 192.313520@f14g 2000cwb.googleg roups.com> from
gumshoo3@yahoo. com contained the following:
[color=blue]
>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.
>[/color]
<snip>[color=blue]
>
>Try to avoid foreign keys in the tables containing the basic data
>(entities).[/color]

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 http://www.ckdog.co.uk/rfdmaker/

[gumshoo3@yahoo.com]

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.