I'm developing a system where people add other people (anywhere from one to thousands of people) as friends, and I was wondering what the best way of doing this is.
Member 1 adds members 24, 377, 554, 59, 6435, 7423, 8, 95, 924339, and 1234 as friends, how can I put that in the database effeciently? I don't want to have a field with 24, 377, 554 in it, doesn't seem efficient.
Thanks!
Full Version: How to store a bunch of data in one field?
You'll need another table, with 2 columns like so
that is the correct way to do it
CODE
userID | friendID
--------------------
1 | 24
1 | 377
1 | 554
1 | 59
--------------------
1 | 24
1 | 377
1 | 554
1 | 59
that is the correct way to do it
Thanks a lot; it seems so simple in my head, but I couldn't think of a way to get it done, that's just what I was looking for.
Make sure they're both indexed and/or primary keys. I usually make them both primary keys when using that type of table lookup. In fact, I call that a Lookup Table. 
It's also called something else, but I can't think of the name right now. There's like 3 different names for it.
BTW Stephen, nice to see you updated your website. Now we can check out your DVD library AND your most recent music tracks!
It's also called something else, but I can't think of the name right now. There's like 3 different names for it.BTW Stephen, nice to see you updated your website. Now we can check out your DVD library AND your most recent music tracks!
QUOTE(malikyte @ Mar 20 2006, 05:01 AM) 
Make sure they're both indexed and/or primary keys. I usually make them both primary keys when using that type of table lookup. In fact, I call that a Lookup Table. It's also called something else, but I can't think of the name right now. There's like 3 different names for it.
BTW Stephen, nice to see you updated your website. Now we can check out your DVD library AND your most recent music tracks!
It's also called something else, but I can't think of the name right now. There's like 3 different names for it.BTW Stephen, nice to see you updated your website. Now we can check out your DVD library AND your most recent music tracks!
You can't make them both primary keys
QUOTE(malikyte @ Mar 20 2006, 05:01 AM)
BTW Stephen, nice to see you updated your website. Now we can check out your DVD library AND your most recent music tracks!
yes yes I know I really should do something with the site. Didn't even mean to upload the music thing to the site; I was just mucking about locally and uploaded the wrong file by mistake a few days later. As you can see its on a different position on every page
QUOTE(Starnox @ Mar 20 2006, 08:49 AM)
You can't make them both primary keys
Yes you can
QUOTE(Think Systems @ Mar 20 2006, 07:51 AM)
Yes you can
No, you can't. Even if you could, why would you? Primary keys are used as identities for rows. You could have one primary key and one unique constraint and so forth, but that's still not two primary keys.
In the UserFriend table, UserFriendID would be the primary key since UserFriendID would always be unique.
UserFriendID | UserID | FriendID
Just use some other constraint if you're looking for a constraint. Otherwise, just index both.
QUOTE(sbauer @ Mar 20 2006, 02:48 PM)
No, you can't. Even if you could, why would you? Primary keys are used as identities for rows. You could have one primary key and one unique constraint and so forth, but that's still not two primary keys.
Well, before I said you can, I decided to test it before posting, and I created the exact table with the two fields as primary keys. I thought that setting them both as the primary key turned the two fields into the primary key.
Edit: I've just repeated it in phpMyAdmin to get the SQL for it:
SQL
CREATE TABLE `test` (
`UserID` INT NOT NULL ,
`FriendsID` INT NOT NULL ,
PRIMARY KEY ( `UserID` , `FriendsID` )
) TYPE = MYISAM ;
`UserID` INT NOT NULL ,
`FriendsID` INT NOT NULL ,
PRIMARY KEY ( `UserID` , `FriendsID` )
) TYPE = MYISAM ;
You can have 2 primary keys though technically it's one primary key working on both fields. That means you can't have the same pair of data twice in the table, but you can repeat either parts of it in other rows. E.g. you could have
5 | yes
5 | no
6 | no
but you couldn't have 5 | yes twice in the table. It's suited to the kind of table Stephen posted where you want to allow the same user more than once and the same friend ID more than once, but don't want to allow the same pairing more than once.
5 | yes
5 | no
6 | no
but you couldn't have 5 | yes twice in the table. It's suited to the kind of table Stephen posted where you want to allow the same user more than once and the same friend ID more than once, but don't want to allow the same pairing more than once.
QUOTE
I thought that setting them both as the primary key turned the two fields into the primary key.
QUOTE(Rikki @ Mar 20 2006, 03:24 PM)
You can have 2 primary keys though technically it's one primary key working on both fields.
Yeah thats what I thought
Although, if you have two primary keys is it not possible to use each of them in seperate relations as candidate keys?
QUOTE(Think Systems @ Mar 20 2006, 10:19 AM)
Well, before I said you can, I decided to test it before posting, and I created the exact table with the two fields as primary keys. I thought that setting them both as the primary key turned the two fields into the primary key.
Edit: I've just repeated it in phpMyAdmin to get the SQL for it:
Edit: I've just repeated it in phpMyAdmin to get the SQL for it:
SQL
CREATE TABLE `test` (
`UserID` INT NOT NULL ,
`FriendsID` INT NOT NULL ,
PRIMARY KEY ( `UserID` , `FriendsID` )
) TYPE = MYISAM ;
`UserID` INT NOT NULL ,
`FriendsID` INT NOT NULL ,
PRIMARY KEY ( `UserID` , `FriendsID` )
) TYPE = MYISAM ;
Is UserID always going to be unique? Is FriendID always going to be unique? You can't create a multiple individual primary keys. MySQL will allow you to create a single primary key that is made from two columns, which is what you're doing.
QUOTE
In database design, a primary key is a value that can be used to identify a unique row in a table. Attributes are associated with it. Examples are names in a telephone book (to look up telephone numbers) and words in a dictionary (to look up definitions).
The primary key should be immutable, meaning that its value should not be changed during the course of normal operations of the database. (Recall that a primary key is the means of uniquely identifying a tuple, and that identity, by definition, never changes.) This avoids the problem of dangling references or orphan records created by other relations referring to a tuple whose primary key has changed. If the primary key is immutable, this can never happen.
The primary key should be immutable, meaning that its value should not be changed during the course of normal operations of the database. (Recall that a primary key is the means of uniquely identifying a tuple, and that identity, by definition, never changes.) This avoids the problem of dangling references or orphan records created by other relations referring to a tuple whose primary key has changed. If the primary key is immutable, this can never happen.
http://en.wikipedia.org/wiki/Primary_key
http://archives.neohapsis.com/archives/mys...06-q1/2167.html
Edit: Just a little slow. Rikki beat me.
QUOTE(sbauer @ Mar 20 2006, 03:32 PM)
Is UserID always going to be unique? Is FriendID always going to be unique? You can't create a multiple individual primary keys. MySQL will allow you to create a single primary key that is made from two columns, which is what you're doing.
http://en.wikipedia.org/wiki/Primary_key
http://archives.neohapsis.com/archives/mys...06-q1/2167.html
Edit: Just a little slow. Rikki beat me.
http://en.wikipedia.org/wiki/Primary_key
http://archives.neohapsis.com/archives/mys...06-q1/2167.html
Edit: Just a little slow. Rikki beat me.
If you read what I wrote - I did actually say I thought that doing this turned the two attributes into one key.
QUOTE(Think Systems @ Mar 20 2006, 10:36 AM)
If you read what I wrote - I did actually say I thought that doing this turned the two attributes into one key.
Sorry. In the morning I actually don't read. I just scim.
For future reference to prevent this kind of misunderstanding... what you are referring to is called a Composite Key.
QUOTE(Wombat @ Mar 20 2006, 03:50 PM)
For future reference to prevent this kind of misunderstanding... what you are referring to is called a Composite Key.
Yeah I know - I've just been reading through the section on relational keys in my Database Systems book to remind me of this all.
As long as you properly index the fields, that should be the most efficient way. If, however, you are not looking to do this, '1,2,5,493' would be the closest you'd get to efficient - FAR more efficient than serializing or pickling.
Wow...I didn't mean to cause this much of an uproar... Geez. Thanks to Wombat for giving the technical term of "Composite Key" since my mind was mush...still is a bit.
Thanks to Wombat for giving the technical term of "Composite Key" since my mind was mush...still is a bit.
QUOTE(Veracon @ Mar 20 2006, 06:28 PM)
If, however, you are not looking to do this, '1,2,5,493' would be the closest you'd get to efficient - FAR more efficient than serializing or pickling.
Maybe, depending on what you want to do with the data. Storing it in that way limits what you can do with it using SQL, and makes the SQL queries you can perform on it more resource intensive and complex.
Oh, and yeah, serializing is rarely ever the best solution.
QUOTE(malikyte @ Mar 20 2006, 11:46 PM)
Wow...I didn't mean to cause this much of an uproar... Geez. Thanks to Wombat for giving the technical term of "Composite Key" since my mind was mush...still is a bit.
Thanks to Wombat for giving the technical term of "Composite Key" since my mind was mush...still is a bit.No probs. Covered it a month or so ago in my Database Systems module so it was fresh in my mind.
QUOTE(Wombat @ Mar 20 2006, 05:21 PM)
No probs. Covered it a month or so ago in my Database Systems module so it was fresh in my mind.
Yeah, that would be almost 2 years now for me.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.