By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,279 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

Massive data and database, how to handle it efficiently?

P: n/a
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?
Dec 15 '07 #1
Share this Question
Share on Google+
7 Replies


P: n/a
This is not a php question.

Please ask in a database newsgroup...or read up on database
normalization
Dec 15 '07 #2

P: n/a
..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
Dec 15 '07 #3

P: n/a
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 help on DB design and normalization.

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

Dec 16 '07 #4

P: n/a
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 helpful 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?
Dec 16 '07 #5

P: n/a
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 helpful 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
<SELECTperformance.

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?
Dec 16 '07 #6

P: n/a
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.
Dec 16 '07 #7

P: n/a
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.

Dec 17 '07 #8

This discussion thread is closed

Replies have been disabled for this discussion.