Database Design: Circular Links

Question

I have three database tables:

• of users
• letters
• Invitations

Emails are associated with users by the user_id field.

Invitations are also associated with users by the user_id field

Letters can be created without an invitation, but each invitation must contain an email.

I would like to link the email and invitation tables so that I can find the email for a specific invitation.

However, this creates a circular link, and both the invitation and the email entry contain an identifier for the same user.

This is a bad design, and if so, how can I improve it?

I feel that using foreign keys and good business logic is fine.

users
-----
id

emails
------
id
users_id

invitations
-----------
id
users_id
emails_id

Answer 1

.

, ().

EDIT:

, , .

: ,

: id
emails: id, users_id
: id, users_id, emails_id

table_id (, ), :

• , .
• , ( : , user_id )

, , .

- , , - . ( ) ( ER, ) , ( ).

- , , user_id , . , , , , , user_id .

, user_id , user_id, , .

, , " " . , email_id user_id, (!).

( ) - , , (, ), , - , , , .

Answer 2

, , FK , . , .

, , , .

, , UserId, EmailId . .

Answer 3

, . - ?

• , . , - - .

• , . "email-id" "", "", "emails". , , , , ( ) .

Answer 4

, .

1 invitation email? , .

, . , , .

( ), user_id invitation user_id email. , . email , user_id. - , unique constraint email.(id, user_id) ( , id - ) . , .

user_id, . - invited_user_id email.user_id email.sent_by_user_id.

Answer 5

I think that you are well versed in all the answers that you already have, I just won’t use the plural table names.

This is confusing and not necessary.

I share the opinion that user_id does not belong to the invitation table, but simply if you are really sure that you want to establish a strong connection between user_id and email_id.

If so, then simply remove user_id from the invitation table, it is useless.