Relationhips Table

[Lou_Reed]

In the file attached is a relationships report for my db. I have two questions on it. The first is as you can see on some tables the primary key is never referenced.



The table tblPropertyPass, tblAttendence have this characteristic. Is this okay or is this a sign of bad db design?


Also, in tblPersonnel, I want to put in the field ysnMeetingID.

It is a primary key in tblMeetings, and a foreign key in tblAttendence. But in tblPersonnel it is just there,
the need for it is clear in the SQL UPDATE code sequence, but it is neither a primary or foreign key in tblPersonnel.

Is the sign of a bad db design or is it acceptable?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[pbaldy]

1) I don't think this is bad design. These appear to be transaction tables, and I don't think it's unusual for their primary keys not to be used elsewhere.

2) I can't see why a meeting ID field would be in tblPersonnel. Presumably a person can attend many meetings, so the attendance table seems like the appropriate place for the meeting ID field. I'd question the ysn prefix, which to me implies a Yes/No field, which this couldn't be.

[Lou_Reed]

The Meeting_ID field is there as a companion to ysnMeeting_Invite_Sent. It is good that we know a meeting invite has been sent, but a ysnMeetingID will tell us for which meeting the ysnMeeting_Invite_Sent applies.

Just to have a notice that the meeting invite has been sent should immediately trigger the question, for what meeting was the invite was sent?

That is why I want to add ysnMeetingID the tblPersonnel?

My question is since ysnMeeting invite sent has been a primary key in tblMeetings and a foreign key in tblAttendence, can I use it again as neither a primary or foreign key?

That is my question?

Any help appreciated.

Thanks in advance.


Respectfully,


Lou Reed

[pbaldy]

I believe the invite sent field should be in the attendance table. A person can be invited to many meetings; the invite relates to the meeting/person, not just the person. If you have the field in the personnel table, it can only record one invitation.

[Lou_Reed]

That is a good point. I will try it out. Thanks for your input.

Respectfully,

Lou Reed

[pbaldy]

No problemo.