Multiple activities in activity_id column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple activities in activity_id column

Author

Topic

Ace111
Starting Member

3 Posts

Posted - 2012-08-09 : 18:22:30

Hi all. I'm very new to SQL programming, less than 6 months of experience, and I've come across a task that wants the social network database I'm working with to do two things...

Have a table of Activities each with their own activity_id (easy enough). Next is have a column in the User table that can store multiple activity_ids, each with their own distinct activity in the Activity table.

What I'm stuck with is how to implement this column. Would one use a SET or ENUM type to do this? If so, how?

An example would be that the activity_id column in the User table would contain 1, 3, 5, 7, 13. This would relate to, say, Archery, Swimming, Boxing, Snowboarding and Knitting in the activity table.

Any help would be appreciated

EDIT: I forgot to mention that the Activity table can reach up to 100,000 distinct activities. So the activity_id column in the User table has to be able to contain a large number of activity ids.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-08-09 : 18:38:35

I like to use bitwise functions for this. Here's an example

DECLARE @Table Table (Activityid tinyint ,Activity varchar(20))
INSERT INTO @Table
VALUES
(1,'Archery'),
(2,'Swimming'),
(4,'Boxing'),
(8,'Snowboarding'),
(16,'Knitting')

select *
from @table t
where activityid & 7 >0

so your column in your actual table would have a value of 7 if the activities were swimming, archery and boxing.

Jim

Everyday I learn something that somebody else already knew

Ace111
Starting Member

3 Posts

Posted - 2012-08-09 : 19:07:20

Ok, I see what you are doing there. Thank you but I need the User table to display each id. I don't think I'm allowed to have a select query such as that.

What I would want the tables to look like is this

For the User table

+------------+
| activity_id..|
+------------+
|.1,3,5,7,13.|
|..2,4,5,6....|
|...1,5,10.....|
|.....2.........|
|...5,6,7......|
|..10,12,13..|
+------------+

Then in the Activity table

+-------------+---------------+
|.activity_id.|.Activity......|
+-------------+---------------+
|......1......|...Archery.....|
+-------------+---------------+
|......2......|...Boxing......|
+-------------+---------------+
|......3......|...Snowboarding|
+-------------+---------------+
|......4......|...Archery.....|
+-------------+---------------+
etc etc...

with a foreign key relationship set up between the two.

Apologies for it being a bit confusing with the spacing and all

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources