DB Design Advice Needed

DB Design Advice Needed

Post by Eric Raski » Sun, 31 Dec 1899 09:00:00



Hello all...

I've got a table of  master table of names/address and a detail table of
orders.  I need to produce statistical counts, such as:

  Last 6 Months orders by SCF (1st three digits of ZIP code)
  Last 12 Months order by SCF
     etc.

  Last 6 Months $10-99 orders by SCF
  Last 12 Months $10-99 orders by SCF
    etc.

Plus a whole lot more of the same thing.  In every case, I just need a
record count for everything that meets the criteria.  Sometimes the criteria
will be in the master table, sometimes in the detail, and sometimes in a
combination of the two.

What is the most efficient way to generate this mess?  Is this a single
Oracle Reports?  Or do I need to run lots of queries first to generate the
individual statistics and store them in tables, then Report them?

Also welcome are places on the web to read up on this type of design....

TIA

Eric Raskin

 
 
 

1. DB design advice needed

Greetings,

I need some database design advice.  I'm setting up a database that will
have a modest number (thousands) of users, each with a modest amount (many
megs, maybe a few gigs) of private data that no other user should see, but
which the system might need to perform global analysis over at some point.

I have three design options:

1.  Put all the user data in a single table with a userid column, and do
all selects with a "where userid=..." clause.

2.  Put each user's data in its own table.

3.  Put each user's data in its own database.

The plusses and minusses as I see them:

Option 1:

Advantages: simplicity, easier to do global system analysis of all user
data (not sure yet how important that might turn out to be)

Disadvantages: security risk: coding bugs more likely to silently expose
user data to unauthorized parties (e.g. by forgetting to put in the 'where
userid=' clause), more difficult to shard if system resource capacities
are exceeded.

Option 2:

Advantages: better security, easier to shard (maybe)

Disadvantages: harder to do global analysis

Option 3:

Advantages: even better security, sharding is trivial (it's already sharded)

Disadvantages: harder to do global analysis, complicates backup

Are there any potential pitfalls in any of these options that I've
overlooked?  Which design would you choose, and why?

Thanks,
Erann Gat

2. Replicating user information

3. Need Db design advice

4. Restoring a database

5. Need DB design advice

6. DBA Tools question

7. Need advice on DB design (Access)

8. SQL Question

9. db design advice

10. Relational DB Design Woes (Any Advice?)

11. Advice on big kid db design

12. Advice needed on Hierarchy Design