PDA

View Full Version : Database backup and deletion

hardigunawan
01-20-02, 07:36
Hello, I'm hoping that someone can help me with this problem. :p I've created a database, consisting of inventory,supplier,branch,sales,allocation.

Inventory: This table is for any goods that the company bought (note that the inventory is not fixed. Every week there would be new type of stocks)

supplier: Is the table of supplier which the company bought from

branch: Consist of all the branches of the company (so that any sales can be tracked where it is from)

allocation: Basically consist of which inventory, how many of it, to which branch

sales: point-of-sale data, such as invoice number, what is bought, how many of it, from which branch, total price etc.

At the end of the year, how do I move out (backup and then delete) the database? Should any inventory that has been sold out be moved out? What about the sales? What if one of the branch is closed down? If it is removed from the database, then from the sales, I won't be able to know from which branch the customer bought from. And for the sales data, should I move out everything for that year? What if the customer came back and stated that the goods is faulty and decided not to buy?:confused:

Since I'm not a database-educated :( I'm confused by all these....:mad:

If there's some other loopholes that I've not seen (lack of experience), can anyone give me some advice ;)

Thank you:D
Manish
01-20-02, 13:38
A lot depends on the following factors:

1. What are the requirements of the users of the database, and how often do they require to access sales records of last year (or for that matter...any records older than 2 months) ? Is it just for checking against any customer complaints/queries or yearly/monthly sales totals?

2. Are the users using a custom Database client system (e.g. developed in VB) which you can easily modify? (That would allow for optimizations that require a change in the client source -- little bit).

3. What is the main purpose of moving out old entries? Space, Speed of querying, or regular archival requirements?


I don't know your stance on above factors, but the simplest suggestion would be to store all data of the last year in tables named Sales2001, Branches2001, etc. You'll then need to modify the client's source to check for the sales number/sales date and accordingly look into the proper year's database.

What's wrong with the above suggestion? A lot of things. It does not buy the approval of experienced hardcode database developers/maintainers. But thats the simplest approach to moving out old databases. Note that if you want to make an extensible, scalable database, then this approach does not suit. However, there may still be many cases suited for this approach.

What is your stance on points 1,2,3 ? -- that would clear up a lot of things and would put you on a better road. :)
hardigunawan
01-20-02, 19:21
Here's my position:

1) The customer would not be looking into the last year's sales, unless there's a customer complaint, and yearly reports.

2) Yes. I'm creating a VB program in order for the customer to manipulate data

3) I'm moving out old entries because of the 3 things you have mention :). Especially space, because in 1 year, there are over 3000 inventories :eek: with new additions coming every week :rolleyes:

I'm confused on what to move out, since the data are inter-related. Moving out branches will destroy the relation with allocation and sales tables :(

Where can I find books/information on this kind of moving out? I've glanced through data warehousing, data mining, but I think it does not answer my questions (or does it? :confused: Is it that I'm too confused to know that?)

thank you :)
Manish
01-24-02, 21:14
If your main concern is space, the only reason users will look up old entries is to find out information about a particular sale, and you are creating the VB program yourself, then one of things you can try out instead of going towards more complex distributed database concepts is moving old sales entries to table called sales2001. Similarly duplicate the other tables as branches2001,etc.etc.

What this will do:

It will allow you to create the VB program and check if the sales record is before 2002, then it should check the 2001 databases, and these database tables may reside on any other disk, any other server.

It will also allow you to remove branches/inventory which have no use for new entries from the existing "current" database.

Where this will fail:

If a branch details are changed, you'll have your program look up and change the corresponding branch details in old records as well. Because we have broken up the tables. Otherwise, you can always instruct your program to first look up details of branch "abc" in the current database, and if it is not found there, then look up in 2001 database.