Multiple Comment Fields vs Comments Table

[Flopper]

Hi,

I'm designing a new database and I'm having a problem with the initial construction of the tables. The DB contains 6 tables, with the primary table containing 14,000 records. Within 3 of the tables i need one or more Comment fields of various sizes (i.e. Some 255, some requiring 750+ characters). Possibly Comments_1, Comments_2 etc.

I'm not sure about the best method of implementing this... should I simply create these additional fields for each table, or should i create a seperate Comment table with a table flag of some variety? For example the table would look like:

CommentID
Comment1
Comment2
Comment3
Comment4
CommentLinkID
CommentType

CommentLinkID would be the primary key of the linked table, and CommentType would be the link to the table (Table1, Table2, Table3).

Any help would be greatly appreciated.

Cheers

 

[fneily]

First read:
Fundamentals of Relational Database Design

http://r937.com/relational.html

Your idea of a comments table wouldn't pass the first normal form. Drop the number and you have repetitive column headings - comment, comment, etc. This may create some comment fields to be empty, what happens when you want to add another comment field (you don't just change the table structure), and it leads to variable length records. So bad idea.

The way memo fields are stored is dynamic. So Access adjusts the storage area itself. You don't need to worry about it.

You did not show your table layouts so we can't tell if they're constructed correctly.

Why would a record have more then one memo field? Why can't users just append to one memo field? More then one memo field may go against the first normal form.

 

[dhookom]

Multiple comment fields would be a poor alternative to multiple comment records. I would also prefer multiple records to comments added to a single comment field.

Depending on your table structures, I might recommend one comment table per other table. I have used systems where one comment table is used across multiple "main" tables and I don't like them.

Duane
Hook'D on Access
MS Access MVP

 

[GingerR]

I'd use one comment table per other table.
In the past, I've used fields

OtherTableRecordID
CommentDate {default = Now())
CommentUserID {form default = GetUserID())
Comment

so there's a record of who entered the comment and when. The Date and UserID are filled in automatically, the user only sees the date when they are entering the comment.


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html