Page 1 of 1

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

Posted: Mon Jan 16, 2006 1:40 am
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!

Posted: Mon Jan 16, 2006 9:12 am
by JayBird
answer to question 2.

You shouldn't be relying on auto_increment for those values

Posted: Mon Jan 16, 2006 9:58 am
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.

Posted: Mon Jan 16, 2006 10:16 am
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`);