By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,035 Members | 1,555 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,035 IT Pros & Developers. It's quick & easy.

holding a vector with in a column of data

P: 7
I am creating a website in which multiple people can be included in one activity. so to do this i thought, Hey! i can just make a vector of the people included, then put that vector in the database for me to view whenever. but i cant find a column type for vector. any ideas how i could make this happen?
May 27 '12 #1
Share this Question
Share on Google+
12 Replies


Frinavale
Expert Mod 5K+
P: 9,731
When you have multiple things to store, you don't want to store them in a column. They should have their own rows.

Put "people" into their own table, give that table a primary key.

Put the "activity" into its own table and give it a primary key.

Then create a link table that links which people are involved in which activity. This table will have 2 columns: the id for the activity and the id to a person.

When you want to retrieve the people for a given activity, use the link/look-up table, joined to the people table where the activity-id is the activity that you are looking for.

Research link (or join) tables, and how to use "JOIN" in an SQL statement.

-Frinny
May 27 '12 #2

P: 7
But what if i dont know how many people are going to be in the activity. and i was to be able to put them in a certain order. i guess i quite dont understand what your saying, (new to database)
May 27 '12 #3

Frinavale
Expert Mod 5K+
P: 9,731
You can use ORDER BY to return the results in a specific order.

There is no limitation to the number of people in an activity with this design.

-Frinny
May 28 '12 #4

P: 7
i think i understand it now. i ad a whole row of information for each person. and them link them all by adding one column with a id for the activity. right?
May 28 '12 #5

Frinavale
Expert Mod 5K+
P: 9,731
You could do it that way too, except then your people are limited to participating in one event.

If that is how you envision your application working then this solution is fine.

If your people can be involved in more than one event, then you'll need the join/link table that I was suggesting earlier.

-Frinny
May 28 '12 #6

P: 7
i dont know how to do that... :P and how will that limit them to one event, i could just put another entry with the event id changing.
May 28 '12 #7

Frinavale
Expert Mod 5K+
P: 9,731
Say you have the following Activity table:
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE Activity(
  2.      ID int NOT NULL PRIMARY KEY,
  3.      Name NVarChar(50)
  4.      [Start] Date,
  5.      [End] Date
  6. )
And the following Person table:
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE Person(
  2.      ID int NOT NULL PRIMARY KEY,
  3.      FirstName NVarChar(50),
  4.      LastName NVarChar(50),
  5.      Activity_ID int FOREIGN KEY REFERENCES Activity(ID)
  6. )
  7.  
Because a person has 1 column for ActivityID, that means that the person can only be in one Activity.

But if you had your activity table:
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE Activity(
  2.      ID int NOT NULL PRIMARY KEY,
  3.      Name NVarChar(50)
  4.      [Start] Date,
  5.      [End] Date
  6. )
Person table:
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE Person(
  2.      ID int NOT NULL PRIMARY KEY,
  3.      FirstName NVarChar(50),
  4.      LastName NVarChar(50))
  5. )
  6.  
And an ActivityPerson link table:
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE ActivityPerson(
  2.      Person_ID int FOREIGN KEY REFERENCES Person(ID),
  3.      Activity_ID int FOREIGN KEY REFERENCES Activity(ID)
  4. )
  5.  
Then your person could be in many activities and your activities could have many people.

-Frinny
May 29 '12 #8

P: 7
so i would have one table called activity, in here it will list all the different activities and its settings including a activity id,
then in another table i have people (who attended) and in there i put the person id, the activity id and they are now linked to that activity by the activity id, i can just ad a row for each person in each activity. kinda like that?
May 29 '12 #9

Frinavale
Expert Mod 5K+
P: 9,731
Yup! Exactly :)

You create a row for each activity in the Activity table.

You create a row for each person in the Person table.

After you've created these rows, and you want to assign people to an activity, you create a row in the ActivityPerson table and simply add the ID of the Activity and the ID of the Person.

You use link/join tables (like the ActivityPerson table) to manage many-to-many relationships like this.

Now you can select from the ActivityPerson table on a known ActivityID and JOIN on the Person table to retrieve the information for all of the people participating in the activity.

-Frinny
May 30 '12 #10

P: 7
how do i link/join tables, i dont get that
May 30 '12 #11

Frinavale
Expert Mod 5K+
P: 9,731
A link/join table is a table that simply contains links to other tables.

In this case, the ActivityPerson table is a "link" table because it forms a bridge/link between the activity table and the person table.

You should research this topic.
May 31 '12 #12

P: 7
oh i was thinking you meant like a actually button you push to physically link the tables.
Jun 1 '12 #13

Post your reply

Sign in to post your reply or Sign up for a free account.