Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

best one table or split peryear table-advices

3 posts in General Discussion Last posting was on 2004-03-13 09:57:42.0Z
sen Posted on 2004-03-10 13:08:37.0Z
Sender: 2da5.404f1070.1804289383@sybase.com
From: Sen
Newsgroups: ianywhere.public.general
Subject: best one table or split peryear table-advices
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <404f1407.2db9.846930886@sybase.com>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 10 Mar 2004 05:11:35 -0800, 10.22.241.42
Lines: 12
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 10 Mar 2004 05:08:30 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 10 Mar 2004 05:08:37 -0800
X-Trace: forums-1-dub 1078924117 10.22.108.75 (10 Mar 2004 05:08:37 -0800)
X-Original-Trace: 10 Mar 2004 05:08:37 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2763
Article PK: 6232

I wish some advices, I need to create an application to
store water and electricity system used for small city,
around 12K houses
what should the best
1. store the invoice table into one table
2. split it into multiple table based on year
the invoice table beside use for transaction also for
generate some report, around 5-10 user will use the table
concurennly

TIA
Sen


Greg Fenton Posted on 2004-03-10 18:00:38.0Z
From: Greg Fenton <greg.fenton_NOSPAM_@ianywhere.com>
Organization: iAnywhere Solutions Inc.
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.6) Gecko/20040113 MultiZilla/1.6.2.0c
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: best one table or split peryear table-advices
References: <404f1407.2db9.846930886@sybase.com>
In-Reply-To: <404f1407.2db9.846930886@sybase.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: gfenton-xp.sybase.com
Message-ID: <404f5879$1@forums-2-dub>
X-Original-Trace: 10 Mar 2004 10:03:37 -0800, gfenton-xp.sybase.com
Lines: 34
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 10 Mar 2004 10:00:29 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 10 Mar 2004 10:00:38 -0800
X-Trace: forums-1-dub 1078941638 10.22.108.75 (10 Mar 2004 10:00:38 -0800)
X-Original-Trace: 10 Mar 2004 10:00:38 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2767
Article PK: 6240


Sen wrote:
> what should the best
> 1. store the invoice table into one table
> 2. split it into multiple table based on year

Relational databases are designed to handled LOTS of data per table.
Indexes and query statistics are used in ASA to keep queries fast. And
just because you have millions of rows in a table does necessarily mean
that queries will have a performance impact...if the majority of your
users are querying and updating "recent" data, then most of that will be
cached by the engine.

Having a growing number of tables tends to make the db harder to
administer. As well, you queries will become more complicated as you
have to dynamically create joins between a growing set of tables.

Following normal design techniques (entity relationships modeling,
etc.), you would likely put all the data into a single entity. Should
the physical design dictate a need for optimizations, you could look to
having "summary tables" for holding summary calculations across large
sets of the data in that single table to reduce the number of table-wide
queries you application need to perform.

Personally, I would stick to data in one table.

Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/


sen Posted on 2004-03-13 09:57:42.0Z
Sender: 743b.4052da93.1804289383@sybase.com
From: Sen
Newsgroups: ianywhere.public.general
Subject: Re: best one table or split peryear table-advices
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <4052db16.743e.846930886@sybase.com>
References: <404f1407.2db9.846930886@sybase.com><404f5879$1@forums-2-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 13 Mar 2004 01:57:42 -0800
X-Trace: forums-1-dub 1079171862 10.22.241.41 (13 Mar 2004 01:57:42 -0800)
X-Original-Trace: 13 Mar 2004 01:57:42 -0800, 10.22.241.41
Lines: 2
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2780
Article PK: 6251

Greg, Thanks for your advice