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_Cu stomers (e.g JohnSmith_02_03 _Customers)
User_FinYear_Su ppliers (e.g JohnSmith_02_03 _Suppliers)
User_FinYear_Ba nkAccounts (e.g JohnSmith_02_03 _BankAccounts)
User_FinYear_Tr ansactions (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,Suppl iers,
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.
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_Cu stomers (e.g JohnSmith_02_03 _Customers)
User_FinYear_Su ppliers (e.g JohnSmith_02_03 _Suppliers)
User_FinYear_Ba nkAccounts (e.g JohnSmith_02_03 _BankAccounts)
User_FinYear_Tr ansactions (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,Suppl iers,
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.
Comment