Page 1 of 1

Table design...

Posted: Mon Feb 06, 2006 12:36 pm
by gumphfy
I'm thinking of making it possible for users of my site, to add enteries of another table to their "favourites", meaning they would add the id of an entery of one database into another, which they can edit in. The question is, how should the table structure be?
I'm sharing session with PHPBB, that way users of my site only have to sign up once.
But I think I would be able add another user table with the values of their "favourites". I've been thinking about this all day, and I'm not quite sure of how the table design should be, for optimal performance.

In reality I only need 3 rows:
______________
| id | user_id | fav|
|__|_______|___|
|__|_______|___|

id being a unique id for every user, user_id being their PHPBB user id and fav as their "favourites"...
id and user_id haven't coused me trouble yet, but I simple don't know how the fav should be, because fav would containd many values, as the user adds more and more "favourites"

Example:
User x wants to add id 334 to his favs; id 334 goes into the fav row. Next he wants to add id 22 to his favs, 22 goes into the fav row.

Now the value of fav row for user x would be ... 334, 22 ???

And another thing, how would I propperly fetch those values and make them into links, pointing to their respected location?

I hope someone is able to help me, and please do ask me, if anything is unclear...I don't think it's entirely clear to myself. It's kind of hard getting my thought down into words...

Posted: Mon Feb 06, 2006 12:47 pm
by feyd
under normalized, each favorite would have it's own record.

Posted: Mon Feb 06, 2006 2:55 pm
by gumphfy
OK...erm...I'm going to sound n00bish here, but ... normalized??
Could you perhaps elaborate a bit on that, or do you have any suggestible readings which would be advantageous to me?
I'm trying to be as eligible as I can.

Posted: Mon Feb 06, 2006 3:51 pm
by feyd

Posted: Wed Feb 08, 2006 11:23 am
by gumphfy
OK, I've done a lot of reading on this subject, but I still don't really understand how this would work in 'real life', and how I would have more than one value in one row. All I've been able to find, describes the subject on a theoretical basis, but I guess I need some practical examples to fully understand.

From what I have read, many show an example of how user have more that one entery...but this doesn't work for me, see currently I have a user base of around 800 users. Lets say thay all have 50 "favourites"...that's 800 x 50 enteries! Which is the equalient of 40000 enteries on one table! that's a lot! And as far as I know there's a limit to how many enteries one table can handle depending of the values of each entery.

Ideally what I want is this:

Code: Select all

+----+---------+----+
| id | user_is | fav|
+----+---------+----+
| 1  | 2       | 2  |
|    |         |50  |
|    |         |36  |
|    |         |etc.|
+----+---------+----+
And when I fetch the values of the fav row, it would show up like this:
link to -> entery 2
link to -> entery 50
link to -> entery 36
...etc.

Is all this just wishful thinking, or is there a way to accomplish this?

If the answer is still normalization, please give me a 'real life' example of how to do this.

Thank you very much in advance!

Posted: Wed Feb 08, 2006 11:37 am
by Christopher
What you want is this:

Code: Select all

+----+---------+----+
| id | user_id | fav|
+----+---------+----+
| 1  | 2       | 2  |
| 2  | 2       |50  |
| 3  | 2       |36  |
| 4  | 2       |etc.|
+----+---------+----+
And you fetch the values like this:

Code: Select all

$sql = "SELECT * FROM favourites WHERE user_id='$user_id'";

Posted: Wed Feb 08, 2006 11:45 am
by gumphfy
And what if all my 800 users all add 50 "favourites" each, which would generate 40000 enteries?
Can one table handle that, and wouldn't it dramastically slow down my site?

Posted: Wed Feb 08, 2006 11:52 am
by feyd
in a word: no.

No it will not slow down your site. MySQL is built to have huge tables. Some are known to have 150 million entries, and work just fine.

Posted: Wed Feb 08, 2006 12:05 pm
by gumphfy
I see, well ok, thanks for the help guys!

Posted: Wed Feb 08, 2006 2:26 pm
by raghavan20
It is the common one-many problem and I was explaining the same thing in another thread. You have to create entries for each favorite added by an user. The table you have finally come up is normalized in the sense no data is duplicated unnecessarily and you have the basic and few fields in the table for it to work properly. Whenever you come across one to many, many to one, many to many you have consider creating a joining table. Here the table you created itself is a joining table. It basically joins users with favorites by means of a mapping.