473,836 Members | 1,596 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.
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.
Jul 20 '05 #1
3 4521
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.c om> wrote in message
news:2b******** *************** ***@posting.goo gle.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_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.

Jul 20 '05 #2
"Rushikesh" <rb*****@sify.c om> wrote in message
news:2b******** *************** ***@posting.goo gle.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.

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
Jul 20 '05 #3
Rushikesh (rb*****@sify.c om) 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,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.


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
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
674
by: Don Vaillancourt | last post by:
Hello all, Over the years as I design more database schemas the more I come up with patterns in database design. The more patterns I recognize the more I want to try to design some kind of generic design patterns that can be used and shared amongst many sub-schemas. For example, the grouping of entities. I may have the following tables: employee, product and client. These tables have no direct relationship with each other. But...
1
3500
by: Lane Beneke | last post by:
All, New to the list and a relative newbie to PostgreSQL. Please forgive stupid questions. Designing an application server for a work order processing (et al) database. I have a good handle on sequences, referencial integrity, views, & the basics. What books/documentation would you recommend to learn more about...
5
9632
by: trynittee | last post by:
Hello, It's been a while since I've posted. I am an intermediate user of Access. I can read simple VB code, have done complex queries, comfortable with event procedures, designing forms and reports. I have not worked with a split database before. And now I am. The front-end database seems to be called .mdb not .mde. Does this
12
7031
by: nyathancha | last post by:
Hi, I have a question regarding best practices in database design. In a relational database, is it wise/necessary to sometimes create tables that are not related to other tables through a foreign Key relationship or does this always indicate some sort of underlying design flaw. Something that requires a re evaluation of the problem domain? The reason I ask is because in our application, the user can perform x
3
2490
by: vicky | last post by:
Hi All, Can u please suggest me some books for relational database design or database modelling(Knowledgeable yet simple) i.e. from which we could learn database relationships(one to many,many to one etc.....),building ER diagrams,proper usage of ER diagrams in our database(Primary key foreign key relations),designing small modules,relating tables and everything that relates about database design....Coz I think database design is the...
0
2567
by: Laurynn | last post by:
# (ebook - pdf) - programming - mysql - php database applicati # (Ebook - Pdf)Learnkey How To Design A Database - Sql And Crystal Report # (ebook-pdf) E F Codd - Extending the Database Relational Model to Capture More Meaning (1979) # Addison Wesley - Database Design for Mere Mortals chm # Addison Wesley - Refactoring Databases Evolutionary Database Design (2006) # Apress Beginning Databases with PostgreSQL From Novice to Professional...
1
2345
by: abhijitbkulkarni | last post by:
Hello, I am designing a .NET database application that uses 3 tier architecture. Starting initially, this application will be desktop application but I will convert it into a website later but design that I am planning should support both version. Development Environment : VS2008, C# Currently Database supported are MS SQL Server 2005 and MYSQL 5 and design for database support is extensible. This application contains several high...
0
1921
by: sam | last post by:
Hi, Hope you are doing well !!!! One of our clients is looking to augment their team with “Database Architect – DB2" please find below the details and respond with
2
5630
by: programmerx101 | last post by:
Ok, I'm looking for expert advice on this one. I have a database which keeps going into read_only mode. Sometimes it goes into read_only / single user mode. Once it was taken offline completely. This seemingly happens randomly. Out of all of the database I have worked with, this has happened on 3 of them - several times randomly to each. All three of the databases that have exhibited this behaviour have been databases I have written for the...
0
9825
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9673
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10859
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10260
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6984
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5653
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5829
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4463
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4023
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.