Clearly #2, less maint and if you ever need over all queries or a customer
gets combined it should be a lot easier. Also use Oracle not the other one
since this is an Oracle newsgroup.
Jim
"Rushikesh" <rbaiwar_at_sify.com> wrote in message
news:2b29906c.0307142123.1d020801_at_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:40:45 CDT
