[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

Creating a Activites Table and linking it to a User

Hi,

so, so far i have a user table as such.

fields are: user_id, first_name, last_name, address, city_id, date_of_birth and time, email address.

list is quite big their are only 5 people here.

 
1	Tama	Rakete	29 Shipton St.	1	1992-09-04 00:00:00	tamar@example.com
2	Hine	Rangi	18c Point Road	2	1992-06-21 00:00:00	hinerangi@example.com
3	Mike	Tupou	95 Collins Crescent	1	1992-02-12 00:00:00	tonganmike@example.com
4	Iosefe	Johnson	2 Henson Street	3	1991-11-12 00:00:00	ijay@example.com
5	Fiona	Cobber	43 Karamatta Street	5	1993-04-17 00:00:00	cobweb@example.com
			

Open in new window


the city_id links to my City table which i have created. what i need here is to make a new link called activity_id which will link to my Activities table which will hold a list of many activities lets say will never be more than 100,000. the issue i am having is some of the users will link to the same activity, but also some users will link to multiple activities.

So i was thinking maybe having activity_id as an enum or SET field, but not sure if this will work as in my Activities table acticity_id will be a auto-incrementing INT.

can someone please help me with step by step instructions.

So far i have created the Activites table with an INT activity_id and a varchar(80) activity fields. no other fields in this table. The table is currently empty, and will be populated after the link has been established correctly.
0
Vishesh1990
Asked:
Vishesh1990
  • 9
  • 6
1 Solution
 
EvilPostItCommented:
I would use a membership table to sit in between the activities and user tables. Create a foreign key constraint from both tables to your membership table and this will enforce the data integrity too.
0
 
Vishesh1990Author Commented:
hey EvilPostIt, so would i store the actual names of the activites in the memebership table, and use to seperate foreign keys to link to it? Also how would i get around mutliple activities for 1 person?
0
 
Vishesh1990Author Commented:
also would i be okay to use enum, if so how would i make the activity_id colom so i can have multiple INT which will link to multiple activities.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
EvilPostItCommented:
The membership table would have 2 columns...
1 - User_ID
2 - Activity_ID

You could add a primary key column too, but to be honest you could just as well make the user_id,activity_id the primary as this would enforce the uniqueness, unless of course you want to allow multiple memberships to the same group for a single user.

The respective table would link the their foreign key, this would allow the membership table to have multiple members for a group and multiple groups for a member.
0
 
Vishesh1990Author Commented:
so each user_id could be in multiple activity_id fields? how would i go about entering the multiples into 1 colom?
0
 
EvilPostItCommented:
You don't have them in 1 column. They are in multiple columns.

For example.

Activities
1 - Maths
2 - Science

Membership Table Example

User_ID,Activity_ID
1,1
1,2
3,1
5,1

According to the above table example and the table you originally published, Tama Rakete is a member of the Maths & Science activities. Also Tama, Mike and Fiona are members of the Maths activity.

Thus fulfilling the requirement of multiple groups per member and multiple members per group.

Does this make sense?
0
 
Vishesh1990Author Commented:
ah... yes thats it, thank you very much, im new to this, so understanding teh terminology is a bit over my head at the moment. this made perfect sense. full marks for you.
0
 
EvilPostItCommented:
Glad i could help.
0
 
Vishesh1990Author Commented:
also one quick question, say my activities list will grow to 100,000 but never more. would it be most efficient to use mediumint(6) or mediumint(5) or even something elst as the data type. From what i can see mediumint(5) will go upto 99,999 but it will not include 100,000 so thats why i need 6. is there a more efficient data type?
0
 
EvilPostItCommented:
Sorry, this is not a SQL Server data type so cant really help.
0
 
Vishesh1990Author Commented:
okay so wat would be a good data type to use for this case?
0
 
EvilPostItCommented:
INT

The reason i say this is because smallint (Which is 2bytes) only goes up to 32,767. Whereas INT (Which is 4 bytes) goes up to 2,147483,647. I understand that you will never reach this limit but the only other option would be to use numeric or decimal where you can specify the length of the number in the precision section. Only problem is that the smallest a numeric or decimal data type can be is 5 bytes... ie larger than an int. So from a storage perspective you would use more space for a more limited option.

So this is why i would choose int. Also it gives you more flexibility in the long run.
0
 
Vishesh1990Author Commented:
okay thanks, will give this a go tonight and see how i get along.
0
 
Vishesh1990Author Commented:
hey EvilPostIt, everything worked out great. thanks for the tips. It took me a while to do it as the database crashed for almost 4 days.   Anyway ive got another question regarding the same database, which i will post as a new thread.
0
 
Vishesh1990Author Commented:
here is the link if your interested.

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_27834039.html

im thinking a new table which acts as a link (similar to the membership table) you suggested last time.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now