Database Design

Results 1 to 2 of 2

Thread: Database Design

  1. #1
    Join Date
    Dec 1969

    Default Database Design

    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.<BR><BR><BR>I)User table: User_id, UserName.....<BR> Users (e.g. John Smith) Each User would contain a following Group of tables<BR> <BR> a)Customers<BR> b)Suppliers<BR> c)Bank Accounts<BR> d)Transactions<BR> <BR> <BR> Tables under :<BR> User_FinYear_Customers (e.g JohnSmith_02_03_Customers)<BR> User_FinYear_Suppliers (e.g JohnSmith_02_03_Suppliers)<BR> User_FinYear_BankAccounts (e.g JohnSmith_02_03_BankAccounts)<BR> User_FinYear_Transactions (e.g JohnSmith_02_03_Transactions)<BR><BR>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.<BR><BR>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?<BR><BR><BR>An alternative design can be as follows<BR><BR>Tables:<BR>Users, Customers, Suppliers, BankAccounts, Transactions .....and so on.<BR><BR>User: User_Id, UserName, ......<BR>Customers: User_Id, Customer_Id,......<BR>Suppliers: User_Id, Supplier_Id,.....<BR>BankAccounts: User_Id, BankAc_Id,.....<BR>Transactions: User_Id, Trans_Id......<BR>.<BR>.<BR>.<BR>.<BR><BR>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.<BR><BR>WHICH DESIGN SHOULD BE USED AS FAR AS SPEED OF SERVER IS CONCERNED? PLEASE HELP WITH CONVINCING REASONS.

  2. #2
    Join Date
    Dec 1969

    Default Generally, fewer tables are better

    You get efficiency of scale by creating indexes in meaningful ways on those larger but fewer tables.<BR><BR>And you&#039;re wrong about the record fetching time growing larger. If tables are properly indexed, then the time will be nearly constant.<BR><BR>Why do you think "the complete table will be loaded in memory"??? I don&#039;t know any decent relational DB--even little old Access--that does that unless you are really fetching all the records in the table.<BR><BR>BUT...<BR><BR>But the bigger problem you have is security: You have to convince your customers that even though their data is mixed into the same tables as other customers that nobody can get to it. Many customers wouldn&#039;t even be happy if you put their *tables* in the same database! Many savvy ones would insist that they have their own secure database. (Of course, you can then argue that savvy users won&#039;t use your online system, so they aren&#039;t your customers, anyway.)<BR><BR>But are there definitive answers? I don&#039;t think so. Except that 20,000 tables is really pushing things.<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts