Re: Database Design

Something else!

I would also recommend that you start using a version control system at the earliest possible stage as this may will save you alot effort later on!

John

"John Bell" <jbellnewsposts_at_hotmail.com> wrote in message news:3f13b349$0$15032$ed9e5944_at_reading.news.pipex.net...
> 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 - 03:02:54 CDT