PDA

View Full Version : design database for multiple offline site

prd00
09-07-02, 02:57
Hi.. I would like to ask some advice on database design.
First, i would like to let you know the structure of my company...It is kind of retail company with small outlets here and there, and lots of them, some are kiosk which doesn't even has any phone or any wired connection to outside, and each outlet could have around 500-1000 total transactions per day, and there are about 9 kind of transaction each outlets, and each has master-detail form.
1. Sales (This holds the biggest number of records. About 95% of total transaction)
2. Incoming
3. Stock transfer out
4. Stock transfer in
5. Return
6. Rejected
7. Promotion
8. Correction
9. Price and discount change

We have about 90 outlets nation-wide, and each outlet will have to report their data back to our server by floppy or CF or any media available.
The system running now, I have a total of 1620 tables inside our server to accomodate these outlets. Processing time to round up all of outlets sales and transaction, also to redistribute specific item become a lot longer. These 1620 doesn't include master database which consists of user, outlet names, etc. For example, to look for how many total of stuff X existed in all our outlets needs 29 seconds to complete, and to find out total sales nation-wide of stuff X taking more time, about 50 sec or so. Also, if we open a new outlet, we need to create another 18 database to accomodate the new one.

My question is,
If I merge all outlets transaction into 1 database, so we have 18 database total, will the performance increase, or even decreased, Considering of a big number of records one database will hold?

Also, if they are all merge into one, How can I keep track of master details records in efficient way, because most of them has duplicate transaction ID. I can only keep the transaction ID unique within only one site, because most of them are offline. So, at outlet A 1 Jan has Sales Id no 1, and perhaps at outlet B 2 Jan also has Sales ID no 1.

Any suggestion on how to increase, or change the structure of this database to make it more efficient and faster? I am combining PHP, VB and MS-SQL Server 2000.

Another question, I am planning to use MSDE redistributable installed on Duron 850 with 128MB of DDR-RAM for each outlet that needs to be a standalone computer. Is that sufficient? Or may be anyone has a better suggestion?

Thanks a lot for your help
saleem
10-06-02, 08:47
make trans ID key combining the outlet ID so that no trans ID get duplicate ID...and then merge all trans at your end
prd00
10-07-02, 06:51
Thank you.. I am sorry but I want to ask another question. If I combined all outlets into one, th record count on one database will get very high. Will that affect database performance? How do I decrease access time to access those database?
I have made some test database according to your advise, and checking maintenance plan for query. I made combined 9x2x12 database for transaction for each month, combining outletID and Trans ID.
So, I tries to combine month August to October in view, from all outlets. The record number is very high, and the maintenance plans show that MS SQL Server made union for all databases from month August to October, even if I ask only month September. Is there any way to optimize search plan?
Also I tried to put database name into variable to make the request as effective as it can like below, but it keeps saying invalid object name.

declare @month as tinyint
set @month = 9
select * from Sales_@month

Can you help with that? So, I want to select the database creating the view according to needed date.

After I tried to combine the records splitted in month, the access time is still high, about 10-15 secs for August to October records only. Is there another way to make this as efficient as possible? Can we do by splitting the files onto different hard drive? Can you give me suggestion how to split it up? Thank you.