Re: Question about database design and Max number of tables in Oracle

On Wed, 4 Oct 2000, Manish Asnani wrote:

> One approach which helps solve the problem is to create a schema which
> stores the metadata for all possible items which can be accepted. So, this
> metadata would store definitions of all possible items such as Cars,
> Cameras, Music,........etc. The main problem with that is the efficiency is
> very bad.

What do you base this statement on? If you haven't built it and stress tested it, how do you know it will be slow?

Just because the brute force method might be faster does not mean that the data driven approach will not perform acceptably.

7000+ plus tables will create a never ending maintenance nightmare.

Jared

>
> Hi Oracle gurus,
>
> We have a database design question which has me challenged.
>
> Here is the situation :
> The goal of the system is to be able to model any possible item. This is a
> consumer oriented site (kind of like Priceline's Perfect Yard Sale) where a
> user can enter what they have to be sold in a garage sale. They can also
> enter what they are looking for. An example is that a user might be
> interested in buying a 2 yr old Nissan Altima which is white or grey color
> and mileage is between 1000 and 23000. Another user might want a CD or a
> book or any consumer type item.
> So the main challenge is to create the data model which will accommodate
> this requirement. What makes this requirement more challenging is that we
> need to be able to search against this data in real time. A user might want
> to search the inventory, for example, to find all cars which have between
> 2000 and 5000 miles.
>
> One approach which helps solve the problem is to create a schema which
> stores the metadata for all possible items which can be accepted. So, this
> metadata would store definitions of all possible items such as Cars,
> Cameras, Music,........etc. The main problem with that is the efficiency is
> very bad. One brute force method is to create individual tables for all
> possible item types which we can define. So we will have tables for Cars,
> Cameras, Music etc. The main problem here is we would be dealing with a
> large number of tables. Our analysts project about 7000 tables. My question
> is - how does Oracle handle these many number of tables. What are the max
> numbers. If anyone has any experience in this kind of a situation, I would
> be glad to receive any tips.
>
> Thanks for your time.
>
> - Manish Asnani
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Manish Asnani
> INET: MAsnani_at_Bonanza.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;-) Regence BlueCross BlueShield of Oregon
jkstill_at_bcbso.com - Work - preferred address Received on Thu Oct 05 2000 - 13:59:11 CDT