Long Text Tables

[Thales750]

I tried to search this didn't find anything relevant.

In a system where you are required to develop to avoid into the Maximum Open Databases limit.

Is it better to have more "Notes" (Memo Data) tables or longer ones. A case could be made for each approach, so I welcome opinions on this?

 

[theDBguy]

Hi. The more tables you open at the same time, the sooner you'll get to the "Maximum Open Database" limit.

 

[Thales750]

Hi. The more tables you open at the same time, the sooner you'll get to the "Maximum Open Database" limit.

I get that part.

If the data in these Notes Tables is mission critical, and will grow to tens of thousands, would you keep mission critical notes in separate tables instead of just one?

Is there any kind of rule?

 

[theDBguy]

Without really knowing the situation, I'll say I would tend to use a single table for all similar/common entities. So, if I need to store information such as "critical notes," then they would all go into one table even if I end up with millions of them. Again, a single table is only counted as one towards the limit.

 

[isladogs]

Bear in mind you can in theory have up to 255 open connections at any time though Access will slow to a crawl long before you reach that stage.
Each open table contributes towards that total whether opened directly or in a query/recordset/form or report.
However if you close all items after use, the connections should be released provided your code is correctly written.

I would personally use fewer tables & filter these as necessary for better performance. However, I think this is likely to be a very minor issue compared to other aspects of database performance.

If you wish to monitor database connections in use have a look at this Available Connections utility based on code written over 10 years ago by Ben Sacharich (and originally brought to my attention by AWF member moke123)

 

[Thales750]

In this case, I decided to add one more Notes table. That makes a total of 3 in this database.

The first one cannot get corrupted or the client will stop all work, they use the notes in that table to convey all information from their customers and the subcontractors. Including emails and calendar event in outlook. That table is the source for all that data.

The second Notes table has barely been used and is of a format and name I don't like. Eventually I may disconnect it from existing queries and re-link them to the new table.

The new table: I Named it "tblUniversalNotes" it has a dual foreign key so any table in the system can link to it. It can be used as a, 1 to many, a lookup, or both. So decision made.

Thanks for the input guys.

 

[theDBguy]

Hi. Good luck. Please keep us posted of any updates as you continue to use this type of setup. It might be interesting to learn any advantages or disadvantages after normal use. Cheers!

 

[Thales750]

I thought I would make a note on duel foreign keys.

Every table in our databases have a field named xxTableID it is a number long field. The "xx" is a naming convention referring to the table the field is located in; all of our fields have that.

The xxTableID field contains the number for that table. It is set in default and never changes. In essence, it becomes a primary key for a table, not a record. It allows multiple tables to reference one table like Phone Numbers or Notes.

We also have a table named tblTableNumbers this table has a description for each table in the system and their TableID Number. It can be used as a lookup for the table Names when viewing queries that contain the TableID foreign key.

 

[isladogs]

Do you mean every record in that table contains an identical value for xxTableID?
Have you considered instead using the unique ID field value in MSysObjects that exists for each database object

 

[Thales750]

Do you mean every record in that table contains an identical value for xxTableID?
Have you considered instead using the unique ID field value in MSysObjects that exists for each database object

That would be good . It would require a lookup on every insert query so I think maybe some addition work. Maybe some kind of module would work.

 

[Thales750]

Do you mean every record in that table contains an identical value for xxTableID?
Have you considered instead using the unique ID field value in MSysObjects that exists for each database object

I did remember a compelling reason to put the xxTableID in every table. It allows for duel field Joins. Otherwise queries will require a criteria for the xxTableID number. Which is OK most of the time, but in many cases it will require an additional layer of evaluation.

 

[isladogs]

Sorry but I can't see the point of this at all. Maybe I'm missing something obvious?
Tables have unique names. Why do you need a unique long number field as well?

Perhaps you have developed a very clever way of managing your tables but, from the description you've given so far, to me it sounds like redundant data.
Possibly it would help if you uploaded something to help explain the idea.

 

[The_Doc_Man]

From the description, we have a single NOTES table leading back to multiple related tables, but the multiple tables are ALL parents to a hodge-podge of notes. The different parents are not necessarily related to each other (from the discussion as I understood it) so the only way to keep from getting cross-over records in the multiple JOIN queries needed for this is to have a compound key that combines the tableID key with the PK of that table (and perhaps a sequence number). Otherwise, without the presence of that table ID, you might have accidental overlap between notes for two different tables.

We had a lengthy discussion on a similar topic on the forum having to do with splitting tables to form adjunct note tables.

https://access-programmers.co.uk/forums/showthread.php?t=300081&highlight=notes

I think the consensus was that separating the tables was better only to keep from totally screwing up relational integrity. That is because with the combined notes table, you CAN'T enforce RI.

 

[Thales750]

That is correct Doc Man.

We don't really use the built-in referential integrity anyway. The vast majority of data input is done with some form of a query, and there are no deletions allowed anywhere in our systems.

 

[Thales750]

Sorry but I can't see the point of this at all. Maybe I'm missing something obvious?
Tables have unique names. Why do you need a unique long number field as well?

Perhaps you have developed a very clever way of managing your tables but, from the description you've given so far, to me it sounds like redundant data.
Possibly it would help if you uploaded something to help explain the idea.

The process described here is also useful for making User Edit Logs. All changes can be recorded in one Log Table.


We have taken the concept even further. Although, it has limited utility, and there are other ways to accomplish this:

We call this process Liquid Notes.

We have a Notes Table designed to house Memo fields.
we have Linking Table that has at least the following Fields:

tblUniversalLink

From:
ulUniversalLinkID
ulUniversalTableID
ulNotesID

it could be expanded to provide Universal to Universal "Many to many", literally

To:
ulUniversalToID
ulUniversaTableToID

The idea originally came from a need to to share Notes between Users and then make a connection to the Notes for the sake of keeping a trail of use. Like Customer Service may need to look at it and maybe when giving a refund or any number of other reasons. We use the one way one often, but the two way one which would allow child table flexibility may have never been used.

It seems that we have only ever needed to link one table's records with multiple Parent Tables, not multiple Child Tables.

I just looked at a Universal Link table; it was empty. So Multiple Parent, Single Child is the practical application.

 

[Pat Hartman]

In a system where you are required to develop to avoid into the Maximum Open Databases limit.

Good application design would control this. Users get confused when too many forms are open at once and so most applications limit the number of open forms. Once you do that, the problem of too many tables open goes away.

We don't really use the built-in referential integrity anyway.

RI is there for a reason. The reason being to protect the integrity of data. It's a dirty job but someone has do it. If the database engine does it in one place, you don't have to do it in hundreds of places.

I don't like the multi-use solution you have chosen but at least you have made it possible to use RI by defining a unique tableNum and an intermediate m-m table. To enforce RI, you need to include that dummy field in every "from" table. That way you can make the tableNum and FK unique in the m-m table and enforce RI on the two column unique index. Normally RI involves the primary key of a table but in this case, you would reference the unique composite index.

 

[Thales750]

I agree about the Maximum. We have been using more popups and less subforms behind Tab Controls. Multiple subforms on Pages of Tab Controls should be avoided for a number of reasons. It is about the easiest way to bloat a system.

It takes building one before you believe just how bad it gets.

Having orphaned records is not any kind of problem at all. Like I said, we use queries for data entry, and never allow deletions.

Never allowing deletions is a fundamental requirement of mission critical data. I can't even imagine the problems that would cause.

"What happened last week, when we sent that shipment to Algeria without Prepay?

I don't know, what shipment?

Yeah, not good.

 

[Pat Hartman]

Someone can and will break RI on any app where you have rolled your own. It is hubris to think you can do this better than the database engine.

 

[Thales750]

Someone can and will break RI on any app where you have rolled your own. It is hubris to think you can do this better than the database engine.

It's not a matter of better. It's a matter of more useful.

1. This Multiple Field Foreign Key arrangement has proven to be very effective in creating much needed flexibility.

2. Using Queries to replace putting Forms in Data Entry Mode has made them almost bullet proof when it comes to following business rules, and controlling the creation of records.

3. Replacing the traditional "Many-to-One" relationships with Many to Many, allows database design to mimic real world systems. Many to Many by definition can't really be controlled by the built in RI.

4. After so many examples of where it is not available to use, we simply no longer rely on it.

It may be arrogance, as you say. But, what we do works, and in the last couple of years, hundreds of thousands of records have been put into our Access Databases and none of them have the problems you describe.

The main thing we do differently is to force All New Records to be entered through some kind of Query. Fill-in a bunch of unbound fields, on an unbound form, and voila, no direct access to tables = no missing data.

 

[essaytee]

...snip...
3. Replacing the traditional "Many-to-One" relationships with Many to Many, allows database design to mimic real world systems. Many to Many by definition can't really be controlled by the built in RI.

I have to disagree, I utilise the RI (including many to many) in nearly every application. It works, it does what it's supposed to do. It might not be my first line of defence against incorrect data input but at least I know my error trapping routines will pick it up.

Do you have a scenario or test db that confirms your assertion that a many to many can't really be controlled by the built-in RI?

The main thing we do differently is to force All Data to be entered through some kind of Query. Fill-in a bunch of unbound fields, on an unbound form, and voila, no direct access to tables = no missing data.

Yes, easy to do, and I do it myself for some applications, though have to qualify easy. There is a tad more coding involved but there is a basic template to follow so creation of new forms using this method becomes straight-forward. I still, as mentioned above, enforce RI of the database engine.