Table design...

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
gumphfy
Forum Newbie
Posts: 23
Joined: Fri Jan 06, 2006 10:18 am

Table design...

Post 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...
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

under normalized, each favorite would have it's own record.
gumphfy
Forum Newbie
Posts: 23
Joined: Fri Jan 06, 2006 10:18 am

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

gumphfy
Forum Newbie
Posts: 23
Joined: Fri Jan 06, 2006 10:18 am

Post 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!
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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'";
(#10850)
gumphfy
Forum Newbie
Posts: 23
Joined: Fri Jan 06, 2006 10:18 am

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
gumphfy
Forum Newbie
Posts: 23
Joined: Fri Jan 06, 2006 10:18 am

Post by gumphfy »

I see, well ok, thanks for the help guys!
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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.
Post Reply