create mysql database for user profile

Question

I need to create a database in which there are 3 tables one is FRIENDS second is USERS third is PICTURES. I have one to many relatioship between users and friends. There is one to many relationship between users and pictures table. I have red a question here with heading structuring a database for user profiles but it was lilbit confusing. They have userid as foreign key but i think picture id should be foreign key in users table.

One more question is, can we have 2 foreign keys in one table as i have one to many relationship of users table with pictures and friends table. Sorry im not good in database. I just need some help. Thanks

Answer 1

You can have as many foreigh key realtions as you need. It makes sense to give PICTURES a userid if you want to link more than one picture to a user. If you want each user to have only one picture, you can give USERS a pictureid.

You can do both as well. Refer to USERS.pictureid if you want to see the main picture (like a portrait picture or an avatar) and allow PICTURES to have a userid so you can manage a set of pictures for each user as well. You can also give a user more than one pictureid, for instance a driverslicencepictureid, an avatarpictureid, a portraitpictureid etc, each being a foreign key to PICTURES.pictureid.

You might even consider the possibility to have each picture linked to more than one user, like facebook, which allows each user to be 'tagged' in multiple pictures, but also allows more than one user to be tagged in each picture.

In that case, you'll want a table to attach those two. Define a USERSPICTURES cross table that contains a userid and a pictureid. You can use this table to define cross links between users and pictures.

As I stated before, you can still give each user an id to specific picture in addition to the cross table.

The same goes for FRIENDS ofcourse. Every person can be a friend of every other person. So in this case you maybe could do with the FRIENDS table just storing the relationship between two USERS. Give the friends table a User1Id and a User2Id, and it allows to store the connection between two users (if that is what you want).