Handling Database Cross Foreign Keys -

i have following tables:

user table:

id | username | password | join_date | avatar_image_id 

image table:

id | url | user_owner_id 

image table holds images of posts, articles , user avatars. each image belongs user can edit it. user_owner_id necessary not enough know image user's avatar need avatar_image_id.

does cross foreign key make problem? bad design? , there way solve it?

here couple of options.

option 1

you in image table:-

id url user_owner_id user_avatar_id 

• user_avatar_id column foreign key user table
• user_avatar_id allows nulls, indicating image not avatar anyone
• since each user has 1 avatar, user_avatar_id should made unique

this has advantage can continue treat images generically in code needs first 3 columns. when have specific code used avatar images need consider last column.

this enforces rule "each user has 0 or 1 avatars". perhaps when user first created, don't have avatar yet ok? if every user strictly must have avatar , want enforce in database need option 2.

option 2

how necessary images go in same table? treat avatar images , other images generically same piece of code? if not, consider adding following column user table:-

avatar_url 

this make quick , easy find avatar image given user. however, may complicate image editing code because have consider things stored in image table these special avatar images.

on balance, choose option 1.