For example, 100,000 records, each record has 10 fields and may belong
to one or more categories. How shall I save those records in DB? I
think it is a very typical concern for most online shopping mall.

Option 1)

A table with all 100,000 records, and 10 fields for each record

Option 2)

Many tables by category, each table with all records for this category
and all fields other than category

Users typically browse by category but cross category query is
frequently used, and query may be made on one or more fields.

How shall I do in this?

This is not a php question.

Please ask in a database newsgroup...or read up on database
normalization

..oO(Ming)

>For example, 100,000 records, each record has 10 fields and may belong
>to one or more categories. How shall I save those records in DB? I
>think it is a very typical concern for most online shopping mall.

100.000 records is nothing for a DB, assuming a properly built and
indexed table structure.

>Option 1)
>
>A table with all 100,000 records, and 10 fields for each record

More or less. Since each record may belong to one or more categories,
you will need two additional tables, one for the categories and one to
combine them both (m:n relation). Nothing special so far.

>Option 2)
>
>Many tables by category, each table with all records for this category
>and all fields other than category

Nope. That would be a really broken design.

>Users typically browse by category but cross category query is
>frequently used, and query may be made on one or more fields.

Peanuts. But you should get a good book or at least some readings on the
Web about proper DB design, normalization and such things.

Micha

Ming wrote:
> For example, 100,000 records, each record has 10 fields and may belong
> to one or more categories. How shall I save those records in DB? I
> think it is a very typical concern for most online shopping mall.
>
> Option 1)
>
> A table with all 100,000 records, and 10 fields for each record
>
> Option 2)
>
> Many tables by category, each table with all records for this category
> and all fields other than category
>
> Users typically browse by category but cross category query is
> frequently used, and query may be made on one or more fields.
>
> How shall I do in this?
>

Option 3: try a newsgroup related to the database you'll be using.
There you will get much better on DB design and normalization.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

100,000 is not massive at all. I had a database of about 10 million
records held in approx. 150 tables and served by an old 1990s 386 PC with
64MB RAM, a bare bones Linux OS, and MySQL (nothing else of note running
on it, not even a GUI). Despite the large amount of data and relative low
power of the PC, simple search queries and inserts returned in about a
second or 2, and more complex queries took about 5-10 seconds. I did write
a few queries that took a few minutes to return but these were very
sophisticated and not things I needed to run regularly.

I'd be surprised if the most efficient way was not Option 1. A definitive
answer can't be given without a lot more info.. You need to read about
'database normalization' to learn more and then -afterwards- ask and
discuss the design on a database newsgroup/forum. If using MySQL try their
forums http://forums.mysql.com/ they are a ful bunch.

HTH.


Ming emailed this:
> For example, 100,000 records, each record has 10 fields and may belong
> to one or more categories. How shall I save those records in DB? I
> think it is a very typical concern for most online shopping mall.
>
> Option 1)
>
> A table with all 100,000 records, and 10 fields for each record
>
> Option 2)
>
> Many tables by category, each table with all records for this category
> and all fields other than category
>
> Users typically browse by category but cross category query is
> frequently used, and query may be made on one or more fields.
>
> How shall I do in this?

Matthew wrote:
> 100,000 is not massive at all. I had a database of about 10 million
> records held in approx. 150 tables and served by an old 1990s 386 PC
> with 64MB RAM, a bare bones Linux OS, and MySQL (nothing else of note
> running on it, not even a GUI). Despite the large amount of data and
> relative low power of the PC, simple search queries and inserts returned
> in about a second or 2, and more complex queries took about 5-10
> seconds. I did write a few queries that took a few minutes to return but
> these were very sophisticated and not things I needed to run regularly.
>
> I'd be surprised if the most efficient way was not Option 1. A
> definitive answer can't be given without a lot more info.. You need to
> read about 'database normalization' to learn more and then -afterwards-
> ask and discuss the design on a database newsgroup/forum. If using MySQL
> try their forums http://forums.mysql.com/ they are a ful bunch.
>

A lot depends on how you set things up.

If say you have item with item numbers as primary key, and categories in
a category table, adding an index to the category field of the database
will speed up searches by category immensely.

It is pretty much equivalent to having tables for each category, in
<SELECT> performance.

If you want to search by name a lot, add an index to that as well.


Its a lot easier to optimise a database for routine known access types
than rewrite the whole structure...
> HTH.
>
>
> Ming emailed this:
>> For example, 100,000 records, each record has 10 fields and may belong
>> to one or more categories. How shall I save those records in DB? I
>> think it is a very typical concern for most online shopping mall.
>>
>> Option 1)
>>
>> A table with all 100,000 records, and 10 fields for each record
>>
>> Option 2)
>>
>> Many tables by category, each table with all records for this category
>> and all fields other than category
>>
>> Users typically browse by category but cross category query is
>> frequently used, and query may be made on one or more fields.
>>
>> How shall I do in this?

On Sat, 15 Dec 2007 12:01:29 -0800, Ming wrote:

> For example, 100,000 records, each record has 10 fields and may belong
> to one or more categories. How shall I save those records in DB? I think
> it is a very typical concern for most online shopping mall.

There is a php.db newsgroup, might be good to try there.

--
// This is my opinion.

On Sat, 15 Dec 2007 12:01:29 -0800, Ming wrote:

> For example, 100,000 records, each record has 10 fields and may belong
> to one or more categories. How shall I save those records in DB? I think
> it is a very typical concern for most online shopping mall.

Though this database is nowhere near "massive", the info you're looking
for can be found by searching for "database normalization".


--
I told you this was going to happen.