Global Key across multiple tables

[TheSmileyCoder]

Im designing a hazard log tool. I have a main form containing hazards, subform for consequences and subform for actions.

I have a form for comments, and I want to reuse that as subform for hazards, consequences and actions, such that you can comment on either of the three.

What would be a good way to go about this?

What table setup should I go for? Should I for example make 3 secondary key fields in the Comments table, 1 for hazards, 1 for consequences, 1 for actions?

Can I somehow implement a global ID? Ive tried googling for Global ID, but can't seem to get any good results so if someone has a good link please post it.
My thoughts on global ID would be to have a table with an Autonumber field called GID, and then have the hazard/consequence/actions table link to that, and get their primary key from that table. The benefit would be that when I add a subform thats used for several different items (like my comments form) that I would only need 1 secondary key column to link it to the Global ID. When I query the commetns table for example, I will be doing it from the Hazard form, and thus the query will only return the relevant comments (those comments made to a hazard)
Im unsure though as to whether there are any unforeseen problems with that. Do anyone have experience with global keys in Access?

[ChipR]

Would it be desirable to have multiple comments per item?

Should I for example make 3 secondary key fields in the Comments table, 1 for hazards, 1 for consequences, 1 for actions?

This is the one thing you should not do. If you have a Comments table, it would probably be something like a Type, ID, and Comment.

[nico5038]

Basically there are two design options:
1) Put a comment as field in each table
Pro: No subform hassle and no extra index /key (=space) needed
Con: For showing all comments a UNION query is needed
2) Use a separate comment table as described by ChipR and use a foreign key in each table.
Pro: Just one table with all comments
Con: Many subforms needed, extra indexing and joins needed for getting additional info from the "main" tables.

My preference would be the first option, as it's the easiest in form processing.

Nic;o)

[NeoPa]

If googling for "Global ID" I would try either of "GUID" or "Globally Unique ID".

Be warned. You're likely to find more than you can deal with in any short period.

[nico5038]

One of the options when setting an auto number key field in an Access table is to use for the Field size the value "Replicatio n ID".
It's however often discouraged to use such a key on the web, check out http://www.trigeminal.com/usenet/usenet011.asp?1033 for more info.

Nic;o)

[TheSmileyCoder]

Thanks for all your insights, I appeciate you guys taking the time to read and reply.

I need to be able to store multiple comments per item, as we can have multiple people submitting comments.

I think what I will do is have the Comments table consist of the following fields
KEY_Comment (Primary Key)
ID_Item (The ID of the hazard/consequence/Action)
ID_Type (The ID of the type of item we are commenting.)

I can then set the ID_Type where I use the subform, and have ID_Type as a hidden field.

[nico5038]

I need to be able to store multiple comments per item, as we can have multiple people submitting comments.

Then I would probably add the ID_Person to the Comment table too.

Nic;o)

[TheSmileyCoder]

Thank you Nic.

The comment table also contains data such as:
ID_CreatedBy
dt_DateCreated
ID_ChangedBy
dt_Changed
ID_Status
ID_Type (Internal/external as external people can submit comments via importable excel sheets)

Each comment is further backed up to a seperate table hisTbl_Comment (History table) so all versions of comments are stored.
I just didn't want to clutter up with more info then what was relevant for the thread.