Re: Database Design

Rushikesh (rbaiwar_at_sify.com) writes:
> I am designing a WEB BASED Accounting Software with ASP and SQL
> Server. For this I need some help for the Database design. My design
> is as follows.

As whether you should use SQL Server or Oracle, I don't have an opinion. I come from the SQL Server side, but these questions have the same answer for any enterprise DBMS.

> I)User table: User_id, UserName.....
> Users (e.g. John Smith) Each User would contain a following Group of
> tables
>
> a)Customers
> b)Suppliers
> c)Bank Accounts
> d)Transactions

This is a completely unacceptable solution, and in completely violation of the relational model. Just forget about it.       

> All these tables would be created at the design time only and as a new
> user is created a record is added to the users table. When the user
> adds Customer the record is added to the Customers table... and so
> on.... The problem with this design is that Customers,Suppliers,
> BankAccounts.... etc tables would contain records for all the users
> and thus the record fetching time for a particular user increases as
> many times as there are users in the Database. Another problems with
> this design is that more than one user would be connected at run time
> will access the same tables, and for even a single user the complete
> table will be loaded in memory.

Your assumptions here are entirely correct. Or to be less polite: they are flat wrong in places.

An enterprise DBMS are built for implementing this kind of solution. With proper indexes, the difference in access time to a certain row if you have 100 rows or million rows in the table is neglible. Or if you for that matter have 100 million rows.

Neither does an enterprise DBMS load an entire table into memory, because there is an access to a single row. I cannot speak for Oracle, but SQL Server will read the pages you access into memory, and if one user is very active, all his pages may be in cache, whereas the pages for a user who is on vacation are only on disk. Pages per users? Ah, didn't I mention indexes? It does seem reasonable from you mentioned to have clustered indexes on user ids.

-- 
Erland Sommarskog, SQL Server MVP, sommar_at_algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp