create mysql database for user profile

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

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).

If you trying to creat some sort of facebook 'friends' functionality then you need to reference the same table. For instance your USERS table could look like this:

(userID, name, email, friends,)

the friends field could then reference lots of the users'friends' by listing their relevent (userID)'s like this:

"23, 345, 34, 34, 23"

All in the same field. Any functions you need to write to list friends can then use this with an explode() to gather friend details.

Finally, yes it is possible to have 2 foreign keys in a table, you can have as many as you need.

Altenatively, something more scalable would be to have the following tables in your DB.

USERS FRIENDS PICTURES

The friends table would then list the relationships between two users like this:

FRIENDS TABLE

user1 = 34 <---this is a foreign key referencing the userID from USERS table
user2 = 44 <---this is a foreign key referencing the userID from USERS table

You can have two foreign keys in one table, although based on your description, you won't. Friends will have a Users FK and Pictures will have a Users FK. And that's it for FKs.

There are questions to be raised about your schema to begin with (e.g., if users are friends with users, their friends are still users, so friendships should be established through a many-to-many link table like Friendships), but based on your description, you should be covered in terms of FKs because you don't have 2 FKs in one table.