Database design: circular references
- by SlappyTheFish
I have three database tables:
users
emails
invitations
Emails are linked to users by a user_id field.
Invitations are also linked to users by a user_id field
Emails can be created without an invitation, but every invitation must have an email.
I would like to link the emails and invitations tables so it is possible to find the email for a particular invitation.
However this creates a circular reference, both an invitation and an email record hold the id for the same user.
Is this bad design and if so, how could I improve it?
My feeling is that with use of foreign keys and good business logic, it is fine.