Vishesh1990
asked on
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.
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.
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
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.
EvilPostIt
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.
ASKER
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?
ASKER
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.
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.
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.
ASKER
so each user_id could be in multiple activity_id fields? how would i go about entering the multiples into 1 colom?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Glad i could help.
ASKER
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?
Sorry, this is not a SQL Server data type so cant really help.
ASKER
okay so wat would be a good data type to use for this case?
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.
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.
ASKER
okay thanks, will give this a go tonight and see how i get along.
ASKER
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.
ASKER
here is the link if your interested.
https://www.experts-exchange.com/questions/27834039/Creating-multiple-messages-in-a-social-media-database.html
im thinking a new table which acts as a link (similar to the membership table) you suggested last time.
https://www.experts-exchange.com/questions/27834039/Creating-multiple-messages-in-a-social-media-database.html
im thinking a new table which acts as a link (similar to the membership table) you suggested last time.