How do we historicize some rows in data tables? (1 Viewer)

[Ken Sheridan]

CJ's question is valid and I didn't originally take that interpretation. Are you storing the actual document in the DB file or merely linking to it as an external file?

Absolutely, the same had occurred to me. If large numbers of documents are being stored as attachments, then it's not surprising they have problems. Storing the documents in a folder and using the database as the means of retrieval by storing the paths as text would be the obvious solution if this is the case.

 

[amorosik]

Moving rows to a separate file should only be a method of last resort. If the size of the back end is pushing the limits of Access, it's time to move to SQL server or another client server database as the back end.

I didn't specify the type of database used
And there's no need to do so.Because the problem is identical regardless of the database type used
It could be SQL Server, Oracle, DB2, Postgresl, ... it doesn't change anything
I categorically rule out the possibility of using an mdb/accdb file to maintain reliable data.

 

[amorosik]

Absolutely, the same had occurred to me. If large numbers of documents are being stored as attachments, then it's not surprising they have problems. Storing the documents in a folder and using the database as the means of retrieval by storing the paths as text would be the obvious solution if this is the case.

No attachments are stored in the main database

 

[isladogs]

Almost 20 years ago I made the single biggest mistake of my coding career.
In my largest schools database, I decided to move the data for all student leavers (those transferring to another school) into separate tables rather than mark them as leavers.
I also decided to create archived tables (and therefore archived leaver tables) for all students who left after their final exams.
The tables were in SQL Server so it wasn't done for reasons of database size. It was mostly done to keep the tables containing data for the currently active students a 'manageable size'. Big mistake.

That decision initially required about 60 student tables to be re-created as archived tables, leaver tables and leaver archived tables, transferring and then deleting the data in the original tables. That meant 4 sets of each table.
When data was required for those student leavers or archived leavers, it could easily be retrieved.
So far so good.

However, any structural changes subsequently made to the main tables, also had to be applied to each of the other tables as occasionally students returned to the school at a later date. This happened several times each year.
If I had just marked the students as leavers, none of that would have been necessary.

It sounds like you are considering making a similar mistake.
I would strongly advise against doing what you suggest. Few (if any) benefits. Many disadvantages.

 

[KitaYama]

"..Data doesn't take up too much space on disk.." this has no bearing on the request.

My bad. I had forgotten that you prefer replies to be limited to exactly what you ask, rather than suggesting a better method, even if the root of the problem is that your approach itself may not be the best database practice.

 

[The_Doc_Man]

"..Data doesn't take up too much space on disk.." this has no bearing on the request.

I beg to differ.

FIRST, I wish to be clear that I'm answering this as a member, NOT as a moderator, so this is one man's opinion and other members SHOULD feel free to step in with any contributions. Now to the topic...

You have explicitly discussed wanting to retrieve data for later review AND have mentioned a desire to save space.. WHATEVER method and whatever DB you are using, the problem is the same. If you are using an ANSI database setup, whether it is the '86 or '92 standards, ASCII text takes up 8 bits per character, and if you are using an extended character set, make that 16 bits per character. Unless you were planning to do something with BASE64 encoding to squeeze five characters into four bytes. You lose a lot of punctuation that way, though.

In the final analysis you will either (a) keep the data as-is in your DB and just mark some of it as "archival", (b) somehow compress records via an encoding scheme, (c) store complete or partial archival records in a side/auxiliary DB, or (d) outright delete the records. Or some combination of those things. Method (a) doesn't change sizing at all. Method (c) just "kicks the can down the road". Method (d) eliminates the ability to review old data. Only method (b) helps with sizing and subsequent review abilities.

Have you considered a variant of (b) where you export the data to a DBF and use the ubiquitous ZIP encoding methods? You would have to unzip a true ZIP file before using it, or you could use the Windows option that lets you create a "ZIP" folder to encrypt things. That DOES allow you to open the file in that encrypted pseudo-folder. That would be a space saver.

Keeping selected data for subsequent review certainly is possible but as I said earlier, how you intend to keep it and what parts you intend to keep will factor in heavily on the question of how to save space. These are decisions that CANNOT be made in the abstract. We've discussed many generic ways, but if that's not good enough, you HAVE to be more specific.

You do this a lot. Your "in the abstract" questions often don't go the way you want so you press for other answers. After a while, that tactic becomes annoying - I've seen it before. You have to understand that you are asking data storage innovation questions of a group that specializes in a product created over 30 years ago that has not been massively upgraded since then. And the Windows answer isn't usable because in the abstract, cloud storage and Access don't work together. Other databases have had similar problems. (I've seen it for ORACLE Enterprise Server myself and heard about issues from a couple of other vendors.)

If you have a real-world problem, describe it. We'll listen. If all you intended to do is ask abstract questions, you can anticipate people dropping off the thread fairly soon. I'm not trying to be harsh here, but we've seen this before from you and it often reaches the point of pointlessness. (Did I just create a Buddhist koan here?)

 

[DickyP]

It could be SQL Server, Oracle, DB2, Postgresl, ... it doesn't change anything
I categorically rule out the possibility of using an mdb/accdb file to maintain reliable data.

I'm assuming this is thread specific, because as a general statement it is far, far to sweeping.

 

[sonic8]

Because the problem is identical regardless of the database type used
It could be SQL Server, Oracle, DB2, Postgresl, ... it doesn't change anything

It changes a lot!

The same problem might exist there, but the set of options to resolve the problem is much larger with the DBMS stated above.

In SQL Server you could create your archive table in the same database but in a different data file. From the outside (file system perspective) this will give you the exact same results as moving the table to a different database, but internally all FK relations from the archive table to other tables can remain intact. Your backup strategy can include or exclude (*your choice!*) the archive table and thus can either produce fully transactional consistent backups, or smaller backups. You can write a stored procedure as the public interface to your document record storage. This stored procedure can then decide, based on passed parameters, how to query which table(s).
The above will reduce or eliminate the majority of drawbacks of an Access DB backend and thus invalidate the arguments others pointed out against your approach.

But even the problem might not be identical! With SQL Server you could make the documents table a partitioned table. This will give you (roughly) the above benefits without splitting the documents into two different tables. Your problem will vanish without explicit historization.

I explicitly referenced Microsoft SQL Server in the above. I'm fairly certain similar features also exist with the other stated DBMS.

 

[amorosik]

It changes a lot!

The same problem might exist there, but the set of options to resolve the problem is much larger with the DBMS stated above.

In SQL Server you could create your archive table in the same database but in a different data file. From the outside (file system perspective) this will give you the exact same results as moving the table to a different database, but internally all FK relations from the archive table to other tables can remain intact. Your backup strategy can include or exclude (*your choice!*) the archive table and thus can either produce fully transactional consistent backups, or smaller backups. You can write a stored procedure as the public interface to your document record storage. This stored procedure can then decide, based on passed parameters, how to query which table(s).
The above will reduce or eliminate the majority of drawbacks of an Access DB backend and thus invalidate the arguments others pointed out against your approach.

But even the problem might not be identical! With SQL Server you could make the documents table a partitioned table. This will give you (roughly) the above benefits without splitting the documents into two different tables. Your problem will vanish without explicit historization.

I explicitly referenced Microsoft SQL Server in the above. I'm fairly certain similar features also exist with the other stated DBMS.

Of course it changes, obviously it changes because they're different databases
What I meant is that I don't want to exploit the unique features of a single database server because that would mean being inextricably tied to a certain system
I'd like to find a general solution, assuming the database server is used exclusively as a data store, meaning no triggers, no stored procedures, no internal database languages, but only the basic functionality for maintaining data within the tables

 

[CJ_London]

I will continue to watch this thread with amusement but will no longer take part. It's taken nearly 30 posts and a lot of wasted effort by responders for their suggestions, all rejected or ignored for one reason or another. My final suggestion is to dump the data into excel or csv file - or xml as suggested by DickyP in post #4

 

[The_Doc_Man]

I'd like to find a general solution, assuming the database server is used exclusively as a data store, meaning no triggers, no stored procedures, no internal database languages, but only the basic functionality for maintaining data within the tables

And here we go again. A general solution has been discussed. By avoiding a specific situation, you immediately cut off any vendor-specific solutions. And we have offered variants such as exporting to another format, be it XML, Excel, raw text logs, etc., or finding a way to compress records either individually or using external compression methods like but not limited to ZIP functionality. But if we can't look at anything but general methods because we don't know the environment, you are going to be limited in what answers you can expect.

We've already lost CJL, and I have no idea how many others have quietly stepped away from the topic. Try to look at this question from our viewpoint, where all we have to go on is an apparently shifting and flexible - or merely vague - set of questions that in the end are talking only about theoretical situations. We can take theory only so far before it has be ready for practical implementation, where the real world steps in.

 

[cheekybuddha]

I can't believe you all fall for the same trolling every time!

 

[The_Doc_Man]

I can't believe you all fall for the same trolling every time!

Moderators SHOULD treat all members equally. And until the variability of the specifications slipped out, it WAS a legit question.

 

[cheekybuddha]

Moderators SHOULD treat all members equally.

No argument there, Doc

And until the variability of the specifications slipped out, it WAS a legit question.

Yes, but with the OP's prior form, we all know how the thread was going to develop from there!

 

[KitaYama]

We've already lost CJL, and I have no idea how many others have quietly stepped away from the topic.

You forgot about those who didn’t step in from the start, because they could already see how the OP’s thread would turn out.

 

[The_Doc_Man]

OK, so I'm a sucker for legit-sounding questions. So sue me. But hope springs eternal in the human breast. (A. Pope in 1733 or 1734).

 

[MadPiet]

Of course it changes, obviously it changes because they're different databases
What I meant is that I don't want to exploit the unique features of a single database server because that would mean being inextricably tied to a certain system
I'd like to find a general solution, assuming the database server is used exclusively as a data store, meaning no triggers, no stored procedures, no internal database languages, but only the basic functionality for maintaining data within the tables

I'm sorry, but this is reaching the level of absurd. "I'd like to drive, but I really don't want to use the gas pedal", as that would be "exploiting the unique features of a car". "A general solution without triggers, stored procedures, internal database languages" (like T-SQL or whatever variant you like?)

No matter what database you may think of using to implement this nebulous plan, it will require exactly what you refuse to use: data stores, triggers, stored procedures, views, the whole lot. Welcome to reality.

 

[The_Doc_Man]

@amorosik - as you can see from the tone of your responses, your method of pushing for answers has not been well-received. However, I will not close this thread as it contains some valuable suggestions on the general question surrounding the topic of archiving. These discussions might be of value to newer members. So thank you for at least raising a topic of potential interest to others. Even if things got a bit overboard later.