Best Data Type for Storing Favorites

[Caged]

Hi,

I'm wanting to store a group of id's in mysql wich will be seperated by spaces (1 23 342 232 ) etc. What would be the best datatype to use for this considering they could have anywhere from 1 favorite to 1000.

Regards -Justin

[MattR]

This is a bad, bad idea, and is in violation of the first normal form. Do a Google search for "repeating groups" and normalization to see why.

[Caged]

Hey Matt, thanks for that bit of information.. I was unaware of this. Is this stating that values that are stored within the database should only be single values, and not multiple representation of many values?

In any case, what would you suggest for accomplishing this task?

A user needs the ability to add to his/her favorites. Assuming I have to store this in a seperate table, I need to store the id,userid and article id's (whats being fav'd).

Thanks again

[MattR]

Is this stating that values that are stored within the database should only be single values, and not multiple representation of many values?

In short, yep.

In any case, what would you suggest for accomplishing this task?

A user needs the ability to add to his/her favorites. Assuming I have to store this in a seperate table, I need to store the id,userid and article id's (whats being fav'd).

Thanks again

That is generally how it would be done. However, what would be the purpose of the ID column? What would logically be a good primary key for that table?

[Caged]

Well, Each user would be unique, all though depending on how its done, there would be multiple records with the same userid ie

userid fav_id
1 34
4 2
1 19

I looked at the way www.4images.de does it, and they store it like I had originally thought would work, but in a seperate table from the users table. They generate an md5 hash for a lightboxid , lightbox being favorites group.
There structure is as follows

Field Type Null Key Default Extra
lightbox_id varchar(32) MUL
user_id mediumint(8) MUL 0
lightbox_lastaction int(11) unsigned 0
lightbox_image_ids text YES NULL

They have no primary, but two Keys, one being user_id and lightbox_id.

[MattR]

Well, you have to answer the question "What attribute or attribute combination can uniquely identify a row?"

In your case, you have it correct:

userid fav_id
1 34
4 2
1 19

There is no need to append an ID on the table beacause userid, fav_id combination is unique. So, that would be a perfect primary key.

[Caged]

Thanks for all your help and advice Matt That pretty much sums it up.