creating a social network (table for each user?)

Discussion in 'Web Design and Development' started by osmet18, Feb 12, 2013.

  1. macrumors newbie

    Joined:
    Feb 12, 2013
    #1
    hi i am creating a social network and i have manage to create a database where i can store and retrieve data.
    i have even set up a login page where a user can get into their profile when the data is verified from the database. (i have only created 3 new users)
    the question is: shall i create a table for each new user that signs-up? and if so (as i believe thats what i shall do) what data should be hold in that table i mean apart form their personal details shall i also include their activities in the networking site or should all their activities be in stored in another table?........ if so how can i linked them???

    thanks for your help
     
  2. Moderator emeritus

    robbieduncan

    Joined:
    Jul 24, 2002
    Location:
    London
    #2
    A table for each user? No that is absolute madness! What if you have millions of users?
     
  3. macrumors 6502

    Joined:
    Sep 3, 2009
    Location:
    CT
    #3
    I'll bet his terminology is just off - you probably mean a table for users, with a record or row for each user. This is usually done well with relational SQL databases (structured query language), but as you get large, you may need to look into NOSQL or non-relational databases. Amazon has a bunch of pay as you go with a free tier solutions: http://aws.amazon.com/nosql/
     
  4. thread starter macrumors newbie

    Joined:
    Feb 12, 2013
    #4
    many thanks for your replies.

    well this is just a uni project and at the moment im not considering having more than 20 people signing up. anyway creating the right query could eventually create a table for each user. creating a ERD probably will help though.
     
  5. Moderator emeritus

    robbieduncan

    Joined:
    Jul 24, 2002
    Location:
    London
    #5
    Creating a table for each user would be a unique database design: the normal/correct way is to add one or more rows per user to an existing table. I'd do some reading on database design. Or, if possible, talk to someone in the Informatics/Computer Science department who knows about it.
     
  6. macrumors 68000

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #6
    I'll take this a step further.

    Do NOT create a unique table for each individual user.

    The approach should be to create a table storing user information in fields such as their user ID as primary key, name, password, preferences, etc. and any fields that store the ID of the social network they belong to. In another table store all the social networks each with a unique ID as primary key plus another other fields like network name, etc. You will create a query that joins the two tables based on the social networking ID common to each.

    This is a HIGHLY simplistic example to describe the CONCEPT - in actuality you create any tables you need and determine how they are joined. This means you need to understand the basic concepts of relational DB's and SQL joins.

    Below is a silly example using two tables joined, the example happens to be dogs since I copied pasted from some stupid site. In your mind think dogs as users and the rfid as social network:

    Now that you've created the tables and inserted data, here is a query with results to demonstrate how to join:

    Just like that from a high level view. ;)
     
  7. thread starter macrumors newbie

    Joined:
    Feb 12, 2013
    #7
    thank you guys for all your replies they have been very helpful

    srwebdeveloper what about creating a table called USER where personal details will be stored manually by the user when signing up but adding an ID row with an auto increment value so we can identify each user. now for instance each user will have their own/private friend's contact list so we create a table called CONTACT with CONTACT_ID as the PK for this table; then we should add ID from USER table to CONTACT table as FK right? im thinking about it this way as i believe that each user has their own table for contacts and no one else can share it. do you think this is the right method to do it or maybe doing this will make the database slow.
    also if you agree this is the right approach is it possible that when a user signs up and the database creates an ID in the USER table, then a row will be created in the CONTACT table as the ID from USER table is the FK in the CONTACT table even though the user doen't have any contacts yet?

    I hope you understand what i'm saying, i think i'm being a bit confusing to understand :)
     
  8. macrumors member

    Joined:
    Jun 19, 2009
    #8
    If I'm understanding this right, you're looking for a way to model relationships between users. One way to do this is to use a self referencing relationship.

    Basically you have a table ( maybe called "relationships" ) and that table would have a "follower_id" and a "followed_id". Of course these terms should be semantically appropriate for the kind of relationship you're trying to model.

    Anyway. Then you can use a join to get a list of a particular users "followers".

    Here's an example statement that would return a list of followers of the user with id#1:

    SELECT * FROM "users" INNER JOIN "relationships" ON "users"."id" = "relationships"."follower_id" WHERE "relationships"."followed_id" = 1

    You would use a similar query to get the reverse-relationships.
     
  9. elppa, Feb 14, 2013
    Last edited: Feb 14, 2013

    macrumors 68040

    elppa

    Joined:
    Nov 26, 2003
    #9
    Jamesbot has given an good answer to the first part of your question. I will try and help with the second part...

    The purpose of the foreign key would be to ensure that for every User ID in the Contact/Relationships table there is an identical User ID in the User table.

    Thus it is perfectly valid for a user to not have any contacts at all (and in this case the constraint is not checked).

    Another way of thinking about it: if you have 4x contacts (in the Contact table) then the DB must check 4x User IDs to make sure they are all in the User table. Whereas if you have 0 contacts, then the DB checks 0 User IDs.

    The only thing the constraint ensures is that all contacts must also be users themselves (and be entered in the user table).
     
  10. macrumors 68000

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #10
    Don't need me anymore! These folks are doing an awesome job as to specifics. My advice was high level so the OP can grasp the concepts involved! The community here is the best! <polite golf clap>
     

Share This Page