Add long text field to table or not?

[PDilly]

I didn’t see this coming.


my club membership DB holds member details. The DB has about 6000 records. The single user who administers the DB needs a long text field which over time will need hold perhaps 3000 characters but the entries will only relate to only 20 or 30 membership records.

i had got a separate table set up on a many to one relationship with the member details table but whilst this works, it would be better for the individual text entries to be in one record per member rather than multiple related records.

if I’d seen this coming I would probably have included the field in the members table but that would probably a waste of space for such a small percentage of members.

has anyone encountered a good way to solve my problem?

I built the DB from scratch about 8 years ago but I’m bit rusty these days!

any help welcome

[June7]

Actually, that sounds like a 1-to-1 relationship - if member can have only 1 record in the comment table and a comment record will associate with only 1 member.

Having the field in Members table won't really waste space or not enough to matter https://social.msdn.microsoft.com/Fo...ce-if-not-used.

It wouldn't strictly follow normalization principles but if it makes life easier, tolerate the empty fields - I do. Don't allow empty strings in text fields.

It is a balancing act between normalization and ease of data entry/output. Normalize until hurts and denormalize until it works.

[isladogs]

There is indeed another way using just one table and it allows for changes over a period of time.
Memo / long text fields can show historical data using the column history property.
I've recently written about it in detail in this article on my website: http://www.mendipdatasystems.co.uk/c...lds/4594523656

[Micron]

Here's a consideration you'd end up saying that you didn't see coming: if you ever need to keep historical values (as in having a list of notes/comments) you definitely need to split these off into their own table. If you do that now, you'll be ready for that eventuality. If you never do, no harm done. I'd also consider a date field for the entry (or perhaps edit as well) since multiple comments per membership would likely benefit from having a date field.

[isladogs]

Whilst I'm not suggesting its better than using a separate table, if you use the column history property you really don't need to split the data into a separate table.
However, it relies on a deep hidden system table so is more complex behind the scenes
If you're not familiar with this property, do have a look at my article on the subject

[Micron]

I did and it's impressive. Looking at it from a novice's point of view, I can't see how mining system tables would be better when you could create normal queries based on tables that are not system tables. Then again, if I can be so bold as to claim to be a rung or two above novice level, I wouldn't go the history route either.

[isladogs]

I actually think the ColumnHistory property has merit ...unlike the other recent features that rely on deep hidden system tables - MVF's and attachment fields - which should both be assigned to Room 101

However the big disadvantage as far as I am concerned is that in order to delete previous comments e.g. if something inappropriate was written, it is necessary to open those deep hidden tables and then edit the record(s).
There is no official documentation on doing this and I've deliberately not explained how to do so. In fact, I only found out how to do it by a lucky accident.

Using a separate table to allow for multiple comments over time is a much simpler solution as well as much easier to edit if needed.
But if its rarely going to be needed ...the question is whether its worth having a separate table?

[Micron]

the question is whether its worth having a separate table?

How much does it cost? Less than the hassle of playing with system tables and not (really) being able to update historical records, I'd say.

[isladogs]

I'm not disagreeing with you. Of course, the system table is still a table ...just deeply buried.

For completeness, I should say that there are other workround ways around editing the property:
1. For a single record, copy the record then delete the original. The ColumnHistory property is not transferred.
2. For all records, set AppendOnly to No then (if you wish) back to Yes. All ColumnHistory for that field is lost.
Both are poor solutions. So if its something that's likely to be needed, don't use the feature

On the other hand, if you want historical comments to be kept unchanged, its worth considering.

Of course, using the 'standard solution' of a separate table, previous comments can be locked at form level.

Anyway, apologies to the OP for the digression.

UPDATE:
I've just updated my article on the Column history feature with information about 2 other ways of editing column history without editing the system table
See section 3 in the updated article: http://www.mendipdatasystems.co.uk/c...lds/4594523656

Neither approach is perfect by any means
I really only recommend the approach for cases where editing historical data isn't likely to be an issue in the future

[PDilly]

Hi Guys,

Thank you all for your input. As i'm now at an age where I should be wearing my slippers, smoking my pipe, reading my paper and losing my marbles, I don't want to get too heavily into the complicated stuff.

as it stands, there are two core tables; members details and annual membership on a one to many relationship (ignore the latter). I set up the 'notes' table attendant only to the member details table, again on a one to many relationship. I included in the member details table, a checkbox to indicate that a note exists in the notes table. this is checked automatically when a note is made.

In the notes table i have an ID field which Access can use but I don't, a member number, corresponding to the member details table, a start date field, and end date field (which is redundant), and a long text field. in that respect, I think I covered all my bases. I have spotted that the largest note to date is just over 1000 characters. Given what it will be used for, I can't see that ever being more than 3000.

whilst it worked, I think I didn't really implemented the input form properly. The difficulty was being able to select any member irrespective of whether a note existed and if a note did exist, to display it on the form.

Perhaps I should move this to the form forum!

The original form is based on both tables with membership numbers linked. it allowed me to select any member and add notes and a date which is what was needed then. I simply displayed the results in the notes table.

what I tried and failed to achieve so far, is to select from my combo box, any member whether they have notes or not and if they have, to display the notes. I can't seem to acheive the right query as I either get only those with notes or everyone with multiple notes to select from. Perhaps my mistake is re-jigging the existing form rather than than starting afresh.

I'd be happy to keep the date field and one to many relationship, if there is a way of bringing multiple notes for a member together as a single view. reality it is unlikely to exceed 4 notes per member.

Many thanks for your help and guideance

[isladogs]

LOL
For info, I'm also now at an age where I should be smoking my slippers, wearing my pipe, losing my paper and reading my marbles.
Unfortunately I lack wisdom which is why I spend time investigating all the complicated stuff.


However, I think you are indeed wise to stick with the simpler solution of a separate table and a one to many join

As for your form, try one of the following
1. create a query as your form record source using both tables but with an outer join so it works when there are no Notes
2. Place the Notes table in a subform
Both should work.

[PDilly]

Thanks Isladogs,

I think I should take up crochet! I'd completely forgotten outer joins. Come to that, as I was looking through the DB innards for inspiration, I couldn't fathom out how I wrote it all!

I now have a form with a sensibly working combo box that passes something or nothing to subform as it should.
Hopefully, I can trial and error my way through the rest of the job copying the tough stuff from elswhere in the DB!

I must be appaling rusty.

[isladogs]

Apply WD-40. That'll fix it!

Hopefully, I can trial and error my way through the rest of the job copying the tough stuff from elswhere in the DB!

If you need to reuse code in your DB, trry creating procedures in modules that you can reuse anywhere just just the procedure name

You can do the crochet whilst thinking about the code. Good luck

[Micron]

I don't smoke, have canceled the paper, lost my marbles thus I can't find my slippers. Maybe they are in the refrigerator again...

a checkbox to indicate that a note exists in the notes table

That seems redundant - a null field in a record (where the structure causes blank fields in a record) means no note. No fk (the pk of membership id) in comments table means no note. The inverse is also true. The outer join will take care of related table design approach.

Colin, you know I'm your #1 fan, so don't take my pokes seriously. It's just that sometimes I think that just because you can doesn't mean you should, but I think I understand why you would and your curiosity has served you very well. I just think that many new developers shouldn't even be aware of the system tables at first, let alone use them for any sort of data management. There are safer and more conventional means for managing related comments (i.e. related tables) that don't require any work arounds. I just don't see the sense in complicating what can be a simple approach, but that doesn't make me right.

[isladogs]

Hi Micron

I know of a case where someone put his slippers in the oven to dry them and they caught fire. I'm not quite at that stage yet.
Next week quite possibly...?

I meant it when I said I agreed with you about the ColumnHistory property.
I also agree with the mantra that just because you can, doesn't mean you should (e.g. MVFs, attachments, calculated and lookup fields)
I think ColumnHistory is absolutely fine for use by beginners provided they have no intention of editing the field.
They don't need to know how they work. Its just in my nature to want to know these things.
They certainly shouldn't be dealing with system tables and that's part of the reason why I have no intention of explaining how I edited the tables directly

You may have missed my update to post #9 and the extra section in my Web article. Have a look