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)
3. Stock transfer out
4. Stock transfer in
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.
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?
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?