1.how do i manage this in database? 2.Q on auto_increment

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
silverme
Forum Newbie
Posts: 13
Joined: Tue Sep 27, 2005 4:24 pm

1.how do i manage this in database? 2.Q on auto_increment

Post by silverme »

(php/mysql)
i just started to learn database by myself.

For practice, recently i am trying to make a web contacts program like facebook but definately not that complex.

So far a user can enter and save personal info into the database in the program. The next question coming to me is, how to manage the buddy lists in the database.

Say user Applecat has 70 buddies and user Catdog has 50 buddies. Do I save all 120 buddies(70+50) in a same table in the database, which is like:

Code: Select all

C1                         |      C2           |    id
=================================
Applecat                |   adamB       |    1
Applecat                |   SuanK        |    2
.........                                           |  .....
Applecat                |   BOOM         |   70
Catdog                  |   Brian           |   71
Catdog                  |   adamB        |   72
.........                                       ..........
Catdog                  |   GoldKid       |   120
If it is managed as above, the thing I am concerning is, if I have 500 users, the table grows to about 30,000 lines; it is a table to be acessed by searching very frequently. Would it be a very low efficiency?

Or there are other much better ways to manage these data?

In facebook, they even showed every connection between you and a stranger, like
You :: Nick :: Ryan :: Thomas
You :: Nick :: Jared :: Thomas
You :: Nick :: James :: Thomas

How can they do that? I am thinking if I am still managing my data as the way I talked about previously, there will be a lot of calculation going on by loading one page!


Problem on auto_increment

When I am making this program, adding one user will create profiles on 2 different tables. In these tables, there will be an Identical # for each user in order to keep the related data from different tables. The # is created by auto increment. Now I am adding on more table to keep some other details for the user. There will be 3 tables keeping user data totally.

Somehow by adding and deleting data, The new added table has a different ID# created by auto_increment.

Code: Select all

Table1
==================
 lg_ID lg_username
      1 silverme
      2 lim
      3 thousand
      4 big big
      5 love
==>   8 loves

Table2
==================
 kk_ID kk_name
      1 silverme
      2 lim
      3 thousand
      4 big big
      5 love
==>   8 loves
The increment # in the orininal tables are fine, but in the new added table:

Code: Select all

Table3
==================
 mm_ID mm_username
      1 silverme
      2 lim
      3 thousand
      4 big big
      5 love
==>   6 loves

no good. How do I adjust the auto_increment back to the corresponding number?


Thanks!
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

answer to question 2.

You shouldn't be relying on auto_increment for those values
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Q1: 30,000 lines in a table isn't that large. The relation you've got setup is the best way (IMO) to do it. However, I'd match user id to user id rather than username to username. I would consider it bad form to match on a string.

Q2: ~Pimptastic is right - you shouldn't be relying on auto_increment to ensure the keys are the same value in every table. All it takes is one errant query to throw that whole thing off. You can rely on auto_increment for your main table, but then get the other two tables to reference that entry in the main table.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

use a foreign key, so your table2 has the columns kk_ID kk_name lg_ID. Table3 has mm_ID, mm_username and lg_ID, then select as:

Code: Select all

SELECT `t1`.`lg_username`, `t2`.`kk_name`, `t3`.`mm_username` FROM `Table1` AS `t1` JOIN (`Table2` AS `t2`, `Table3` AS `t3`) USING (`lg_ID`);
Post Reply