Best table structure for a log table

[Moss100]

Hello,

I am setting up a log table.

In short it will record events that happen in several tables.

A clients table, property table and an applicants table.

The events are not related to each other as such.

For example. Client10 with a log note may be in the first record
Property52 may be in the next
Etc....


I will have to store the ID in each record along with the log note.


My options seem to be to have seperate fields for each table ID

Ie. Fields like. LogClientId, LogPropertyId, LogAppId.

... And store the associated unique Id seperately in each field.

OR

I could have one ID field and one Item field. Ie.

LogId. = 50
LogItem = Property.

I would be interested to gather some opionion.

Thank you Mark.

 

[MajP]

You should use the latter because it is a normalized structure.
This assumes all your ID's are the same dataype. Either all numeric or all text. If you have both than you would have to make the logID text and save the numeric IDs as text. This may be fine or not depending on what else you plan to do. If you save numeric ID as text in your log table than you may have difficulty joining the table in a query back to the other table from where it came. You will get a datatype mismatch. In other words "50" will not join on 50.