Table structure for dates of events related to claims

[GS500]

I’m developing a claim tracking database that tracks dates of events that occur in the course of processing a claim; such as, Loss Date, Report Date, Estimate Date, Payment Date, etc.

I currently have the following tables set up:

tblClaim
ClaimID
ClaimNumber

tblEvents
EventID
EventName

tblClaimEvents
ClaimEventID
fkClaimID
fkEventID
EventDate

With this table structure there is a many-to-many relationship between Claims and Events, but, there will only be one of each event per claim. Is there a better way to set up the tables to enforce a 1-to-1 relationship?


I was planning on creating a form for the Claim table with a subform for the Events joined on ClaimID and fkClaimID, but that would allow users to create more than one set of Events per claim, if that makes sense.

Thanks for any help.

 

[spikepl]

but that would allow users to create more than one set of Events per claim

Not really. If you make a composite unique index out of fkClaimID and fkEventID then you cannot marry a given type of event and a given claim more than once.

 

[jdraw]

If it is possible that the same event could happen to the same claim at different dates, then you could include EventDate
in the composite unique index suggested by spikepl

 

[GS500]

Ahh, that makes sense. I kept getting hung up on the table relationships and didn't even think about an index.

Each event will only happen once, at least as far as we're concerned with now. But, there could be multiple payments issued which we might want to track in the future.

Thanks!

 

[GS500]

Hello again, I have another question related to this one.

One of the primary goals of this database will be to track time between events. With the tables listed above, where each date is stored in the same field, it seems like it would be more difficult to calculate the dates between events than if the events were all separate fields, where I could just do a DateDiff("d",[Event1],[Event2]). But I have no idea how to calculate the date difference if the dates were both stored in the same field, so maybe it's not that much harder.

Is there a downside to having the events as separate fields? I know I would have to create another field in my table, forms, queries, reports if I ever need to add another event.

So I guess my question is; what would you do? I’m just trying to avoid getting several hours into this and finding out I set up my tables wrong and having to go back and redo everything.

Thanks for your advice.