Table design...
Moderator: General Moderators
Table design...
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...
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...
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:
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!
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.|
+----+---------+----+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!
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
What you want is this:
And you fetch the values like this:
Code: Select all
+----+---------+----+
| id | user_id | fav|
+----+---------+----+
| 1 | 2 | 2 |
| 2 | 2 |50 |
| 3 | 2 |36 |
| 4 | 2 |etc.|
+----+---------+----+Code: Select all
$sql = "SELECT * FROM favourites WHERE user_id='$user_id'";(#10850)
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
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.