Go Back   MacRumors Forums > Special Interests > Visual Media > Web Design and Development

Reply
 
Thread Tools Search this Thread Display Modes
Old Feb 12, 2013, 09:43 AM   #1
osmet18
macrumors newbie
 
Join Date: Feb 2013
creating a social network (table for each user?)

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
osmet18 is offline   0 Reply With Quote
Old Feb 12, 2013, 09:44 AM   #2
robbieduncan
Moderator
 
robbieduncan's Avatar
 
Join Date: Jul 2002
Location: London
A table for each user? No that is absolute madness! What if you have millions of users?
robbieduncan is offline   1 Reply With Quote
Old Feb 12, 2013, 10:12 AM   #3
dma550
macrumors 6502
 
Join Date: Sep 2009
Location: CT
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/
dma550 is offline   0 Reply With Quote
Old Feb 12, 2013, 10:30 AM   #4
osmet18
Thread Starter
macrumors newbie
 
Join Date: Feb 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.
osmet18 is offline   0 Reply With Quote
Old Feb 12, 2013, 10:32 AM   #5
robbieduncan
Moderator
 
robbieduncan's Avatar
 
Join Date: Jul 2002
Location: London
Quote:
Originally Posted by osmet18 View Post
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.
robbieduncan is offline   0 Reply With Quote
Old Feb 12, 2013, 12:58 PM   #6
SrWebDeveloper
macrumors 68000
 
SrWebDeveloper's Avatar
 
Join Date: Dec 2007
Location: Alexandria, VA, USA
 
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:

Quote:
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:

Quote:
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.
__________________
Jim Goldbloom
Sr. Web Developer, owner GoldTechPro, LLC
http://www.GoldTechPro.com
SrWebDeveloper is offline   0 Reply With Quote
Old Feb 14, 2013, 11:00 AM   #7
osmet18
Thread Starter
macrumors newbie
 
Join Date: Feb 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
osmet18 is offline   0 Reply With Quote
Old Feb 14, 2013, 01:59 PM   #8
Jamesbot
macrumors member
 
Join Date: Jun 2009
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.
Jamesbot is offline   0 Reply With Quote
Old Feb 14, 2013, 02:27 PM   #9
elppa
macrumors 68040
 
elppa's Avatar
 
Join Date: Nov 2003
Jamesbot has given an good answer to the first part of your question. I will try and help with the second part...

Quote:
Originally Posted by osmet18 View Post
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).

Last edited by elppa; Feb 14, 2013 at 02:32 PM.
elppa is offline   0 Reply With Quote
Old Feb 15, 2013, 06:18 AM   #10
SrWebDeveloper
macrumors 68000
 
SrWebDeveloper's Avatar
 
Join Date: Dec 2007
Location: Alexandria, VA, USA
 
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>
__________________
Jim Goldbloom
Sr. Web Developer, owner GoldTechPro, LLC
http://www.GoldTechPro.com
SrWebDeveloper is offline   0 Reply With Quote

Reply
MacRumors Forums > Special Interests > Visual Media > Web Design and Development

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Similar Threads
thread Thread Starter Forum Replies Last Post
"Pic&Click - Social Polling" [Free] - The New Social Network ajnicola iPhone and iPod touch Apps 0 Oct 16, 2013 02:19 PM
Resolved: Creating a blank table view MickeyT iPhone/iPad Programming 11 Mar 4, 2013 07:09 AM
Creating a localized table index on the fly idelovski iPhone/iPad Programming 5 Jul 19, 2012 08:25 PM

Forum Jump

All times are GMT -5. The time now is 07:25 AM.

Mac Rumors | Mac | iPhone | iPhone Game Reviews | iPhone Apps

Mobile Version | Fixed | Fluid | Fluid HD
Copyright 2002-2013, MacRumors.com, LLC