Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Database Design

Database Design

From: Rushikesh <rbaiwar_at_sify.com>
Date: 14 Jul 2003 22:23:40 -0700
Message-ID: <2b29906c.0307142123.1d020801@posting.google.com>


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.

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
	
	
	Tables under :
	User_FinYear_Customers (e.g JohnSmith_02_03_Customers)
	User_FinYear_Suppliers (e.g JohnSmith_02_03_Suppliers)
	User_FinYear_BankAccounts (e.g JohnSmith_02_03_BankAccounts)
	User_FinYear_Transactions (e.g JohnSmith_02_03_Transactions)

As new user is created all the above tables are created at run time. These tables are created for each and every user. There can be more than 4 tables (as mentioned above) for one user. These tables will increase as more users are added. Only thing in support of this design is that, the record fetching time for a particular user would be minimum and the table for a particular user will only load in Memory.

IS IT FEASIBLE TO CREATE ABOUT 20 TABLES FOR EACH NEW USER ADDED TO THE DATABASE? WHICH MEANS IF THERE ARE 1000 USERS THERE WOULD BE 20000 TABLES IN THE DATABASE. THIS CASE CAN GO WORSE IF THERE ARE MORE THAN 1000 USERS. WHAT IS BETTER DATABASE DESIGN, MORE TABLES WITH LESS RECORDS OR LESS TABLES WITH MORE NO.OF RECORDS? An alternative design can be as follows

Tables:
Users, Customers, Suppliers, BankAccounts, Transactions .....and so on.

User: User_Id, UserName, ......

Customers: User_Id, Customer_Id,......
Suppliers: User_Id, Supplier_Id,.....
BankAccounts: User_Id, BankAc_Id,.....
Transactions: User_Id, Trans_Id......

.
.
.
.

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.

WHICH DESIGN SHOULD BE USED AS FAR AS SPEED OF SERVER IS CONCERNED? PLEASE HELP WITH CONVINCING REASONS. Received on Tue Jul 15 2003 - 00:23:40 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US