I'm building the database for my Social Networking Site project. My database has to hold information of many user accounts. Each user has a profile and the profile contains various information. The account owner should be able to set the privacy of each profile information (either private or public). I'm wondering how I should keep track of those privacy option for each profile detail. Is it a good design to have a table called Privacy whose columns are the details and the values are privacy options? Anyone could give me an idea on this? Thanks
A:
User(ID, Name)
ProfileInfo(ID, Name, CanSetPrivate)
Profile(User_ID, Profile_ID, Value, IsPrivate)
pascal
2010-09-09 14:58:03
Thanks for your help. This is what I thought of initially but I was afraid that would result in a lot of rows in the database. I wonder if this structure is common in the real world? Like how sites like facebook, myspace... do
Wadey
2010-09-10 02:15:50
The number of rows is not really a problem. 10 million visitors, 100 profile info, that's only 1 billion Profile rows. But it would probably be more efficient to store a `Profile` column (e.g. structured by using XML) directly in the `User` table, with all the profile information in one place.
pascal
2010-09-10 05:46:13
Using XML structure to store many profile information in just one column sounds good to me. Is there any potential problems with that approach?
Wadey
2010-09-10 08:49:06
If you remove a ProfileInfo (or update its ID), you won't be able to delete (or update) the corresponding values. You won't be able (unable you manage some helper indexes by hand, or your database provides indexed calculated columns) to efficiently retrieve User on their Profile (e.g. EducationLevel>College and BirthPlace near Savannah)...
pascal
2010-09-10 10:56:14
... but I finally notice that you didn't mention SQL in your architecture. This might not be an issue with other databases, e.g. OO...
pascal
2010-09-10 10:59:42
Actually I'm gonna use SQL Server. Using XML data type might cause trouble when I want to do some special queries to the database such as sorting, updating or deleting. They can be done but more complicated. Maybe I will follow the first way you suggested.
Wadey
2010-09-10 12:18:10