[SOLVED] tables and databases

[ricmetal]

newb

i gotta ask

 

i am making

 

a website like facebook

 

i want each user to store info in a db

 

should i create a table for each user, hosted on one single database

or a database for each user?

 

 

[corbin]

If you're really asking that question, good luck making a site like facebook ;p.  Gotta learn somehow I guess.

 

 

Anyway, neither.  You should have tables storing user information.

 

For example, you will know that all of your users will have certain information, a user name, password, and real name for example.

 

CREATE TABLE users (

    user_id INT AUTO_INCREMENT PRIMARY KEY,

    username varchar(32),

    password char(32), --assumes md5 hash used

    fname varchar(32),

    lname varchar(32)

) ENGINE=InnoDB;

CREATE INDEX idx_username ON user (username);

 

 

Then all of your other tables would be mapped back to a user by the user_id.  For example, images would be mapped to their owner through a user_id.

 

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

 

Will probably help you.

[ricmetal]

no no

 

i have that table already to store that users' info

 

now, when each user logs in, they add stuff, like fav movies, fav music, etc.. its that info i wanna know where i should store - if i should have a db associated to each user or just a table in the same db as i have every users' registration data (id, user_name, pass, email, etc)

[corbin]

"should i create a table for each user, hosted on one single database

or a database for each user?"

 

 

I understood that as meaning:

 

"Should I create a new table for each new user, or should I create a new database for each new user."

 

 

You really should read that link on database normalization.  It would answer your question.  But:

 

You should store things in their respective tables.  For example, you should have a fav_songs table, a fav_movies table so on.  Then, you would store a user_id and a song_id.

[ricmetal]

u understood correctly...

 

in reality each user can fill in 5 fields (fav music, fav movie, etc) and create an entry (which creates a row in the db, on a table), but they can create as many entries as they want. so i'm better off creating a table for each user and have various tables with various rows with the 5 entries in one db.

 

 

 

 

 

[ricmetal]

if it is preferable to have multiple tables, over having multiple dbs,

i think so at least its easier to do (my project)

 

[revraz]

Sounds like you are telling us rather than asking, which means do it how you like.

 

A good suggestion was offered, if you don't like that, do it your way.

 

u understood correctly...

 

in reality each user can fill in 5 fields (fav music, fav movie, etc) and create an entry (which creates a row in the db, on a table), but they can create as many entries as they want. so i'm better off creating a table for each user and have various tables with various rows with the 5 entries in one db.

 

 

 

 

 

[ricmetal]

my question was

      'various single-tabled dbs or various tables in one db'

 

organizing info inside one database wasn't really was i was going for...

 

 

[Mchl]

u understood correctly...

 

in reality each user can fill in 5 fields (fav music, fav movie, etc) and create an entry (which creates a row in the db, on a table), but they can create as many entries as they want. so i'm better off creating a table for each user and have various tables with various rows with the 5 entries in one db.

 

You are not better off doing it this way. Such design will drive you crazy sooner than later.

 

my question was

      'various single-tabled dbs or various tables in one db'

 

Neither. But of these two, various tables in one db is less crazy. Both are really bad.

In general, well designed database has possibility to store all data needed without adding new tables as new users register.

[ricmetal]

In general, well designed database has possibility to store all data needed without adding new tables as new users register.

 

example

[Mchl]

This forum's database

[ricmetal]

i think i have found out a way to add all member entries just on one table, instead of creating a table from each member's entries.

 

the result will be a table with all the members entries, where several rows will belong to member X and another several rows belong to member Y.

 

but i get the question: when i have 10,000 rows and i need to fetch all rows associated with  member X, won't this consume as much 'brain' power as it would to have 500 tables and query each individual table when a member wants his rows listed (as an excel list for example)?

[Mchl]

No. It won't. It will in fact work faster for several reasons.

 

Here are at least two.

1: Database engine is optimized to work fast on long tables.

2: Your script will not have to switch between tables (and that usually means switching between files on disk, and that is slow)

[ricmetal]

oh, ok 

thanks mchl