creating a social network (table for each user?)

osmet18 · Feb 12, 2013

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

robbieduncan · Feb 12, 2013

A table for each user? No that is absolute madness! What if you have millions of users?

dma550 · Feb 12, 2013

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/

osmet18 · Feb 12, 2013

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.

robbieduncan · Feb 12, 2013

osmet18 said:
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.

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.

SrWebDeveloper · Feb 12, 2013

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:

CREATE TABLE dog
(
id int(11) NOT NULL auto_increment,
name varchar(255),
descr text,
size enum('small','medium','large'),
date timestamp(14),
PRIMARY KEY (id)
);

INSERT INTO dog (name,descr,size,date) VALUES('Max','Its distinctive appearance and deep foghorn voice make it stand out in a crowd.','medium',NOW());
INSERT INTO dog (name,descr,size,date) VALUES('Jake','It loves human companionship and being part of the group.','medium',NOW());
INSERT INTO dog (name,descr,size,date) VALUES('Buster','Short-legged but surprisingly strong and agile.','small',NOW());

CREATE TABLE rfid_dog
(
dog_id int(11) NOT NULL,
bar_code varchar(128) NOT NULL,
notes text,
iso_compliant enum('y','n') DEFAULT 'n',
date timestamp(14),
PRIMARY KEY (dog_id)
);

INSERT INTO rfid_dog (dog_id,bar_code,notes,iso_compliant,date) VALUES('1','234k34340ll2342323022','This is a RFID tag for the Max','y',NOW());
INSERT INTO rfid_dog (dog_id,bar_code,notes,iso_compliant,date) VALUES('2','09383638920290397d829','This is a RFID tag for the Jake','y',NOW());
INSERT INTO rfid_dog (dog_id,bar_code,notes,iso_compliant,date) VALUES('3','30id8383837210jndal20','This is a RFID tag for the Buster','y',NOW());

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

SELECT dog.id, dog.name, rfid_dog.bar_code AS rfid
FROM dog,rfid_dog
WHERE dog.id = rfid_dog.dog_id
ORDER BY dog.name ASC;

+----+--------+-----------------------+
| id | name | rfid |
+----+--------+-----------------------+
| 3 | Buster | 30id8383837210jndal20 |
| 2 | Jake | 09383638920290397d829 |
| 1 | Max | 234k34340ll2342323022 |
+----+--------+-----------------------+
3 rows in set (0.00 sec)

Just like that from a high level view.

osmet18 · Feb 14, 2013

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

Jamesbot · Feb 14, 2013

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.

elppa · Feb 14, 2013

Jamesbot has given an good answer to the first part of your question. I will try and help with the second part...

osmet18 said:
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?

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

SrWebDeveloper · Feb 15, 2013

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>

Search

Search

creating a social network (table for each user?)

osmet18

macrumors newbie

robbieduncan

Moderator emeritus

dma550

macrumors 6502

osmet18

macrumors newbie

robbieduncan

Moderator emeritus

SrWebDeveloper

macrumors 68000

osmet18

macrumors newbie

Jamesbot

macrumors member

elppa

macrumors 68040

SrWebDeveloper

macrumors 68000

Our Staff