Database Design

[Rushikesh]

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.

[Jim Kennedy]

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" <rb*****@sify.com> wrote in message
news:2b**************************@posting.google.c om...

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.

[Noons]

"Rushikesh" <rb*****@sify.com> wrote in message
news:2b**************************@posting.google.c om...

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.

You may need a lot more than just these tables.

and thus the record fetching time for a particular user increases as
many times as there are users in the Database.
Oh no, it doesn't!
will access the same tables, and for even a single user the complete
table will be loaded in memory.
It is ridiculous if you write your code to do that.

WHICH DESIGN SHOULD BE USED AS FAR AS SPEED OF SERVER IS CONCERNED?
PLEASE HELP WITH CONVINCING REASONS.

Second.
Read a few texts about database design and normalization.

--
Cheers
Nuno Souto
wi*******@yahoo.com.au.nospam

[Erland Sommarskog]

Rushikesh (rb*****@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, so****@algonet.se

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