Re: Database Design

Hi

As Jim says use the second schema, but create an encrypted view that will restrict the data by matching on user name and access the data though stored procedures that use the view. As you are already using SQL Server I would stick to that. Being accounting software you will probably have to meet some form of regulatory criteria and approval, so make sure that you incorporate that in your design at an early stage as retro fitting things like auditing requirements can be a difficult task.

John

"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 - 02:53:21 CDT