One to many relationships in one table
Here is the situation: -
I have a table called Users. This contains user data for students and teachers, as most of the required data is the same.
Having completed the system, I now said that the client would like to be able to assign students to teachers.
Is there a legal / clean way to create one to many relationships within one table, perhaps via a table of links?
I tried to think it through, but any solution I come up with seems to be a mess.
Any input would be grateful.
thank
Phill
source to share
Have you tried the following approach?
Create a new table, for example TutorStudent
(choose a more appropriate name if necessary). It should have two columns:
-
Tutor_ID
-
Student_ID
Both columns must be a (composite) primary key, each column will be a foreign key in your table Users
User_ID
(I assume this is what you have).
So, if you have a mentor named Newton who has two students, Tesla and Edison, your spreadsheet Users
will have something like this:
- User_ID, Name
- 1, Newton
- 2, Tesla
- 3, Edison
and the table TutorStudent
will have the following values:
- Tutor_ID, Student_ID
- 12
- 13
Relatively simple and doesn't require any changes to your existing table.
Be careful when deleting users - use the delete cascade feature of your database system, or do some technical work afterwards so that your table is TutorStudent
not out of date when you update / delete your users.
source to share
My ideal for the same situation
Example: one book has many categories :
Basic solution: information about books is recorded in the table of books , information about categories is recorded in the table of categories, for example: 100 documents table book_category_relation contains one book (book_id), has a category (category_id) 1 book can have 100 category_id
Ideal solution : first calculate the total for your category: out of 100 documents. Each category is equal to 1 bit value: maximum 31 bits, so for 100 category we have a minimum level (100% 31) = 4 groups
category_id = 1 : 1 (1%31) <=> 000000001 group 0 = floor(1/31) category_id = 2 : 2 (2%31)<=> 000000010 group 0 = floor(2/31) category_id = 3 : 4 (3%31)<=> 000000100 group 0 = floor(3/31) category_id = 4 : 8(4%31)<=> 000001000 group 0 = floor(4/31) ........................... category_id = 31: 2^31(31%31) <=>1000..000 group 0 if moduler 31 equal zero so number group = (31/31 -1)=0; category_id = 32: 1(32%31) <=> 0000000001 group 1 = floor(32/31) category_id = 33: 2(33%31) <=> 0000000010 group 1 = floor(33/31)
Ok, now we add 4 fields to the project book table ( group_0, group_1, group_2, group_3 ) with unsigned int (11) and add an index for those fields
if the book has a category id = n, then we can calculate the following formula:
bit code = (n%31 ==0)?31: (n%31)
number group field = (n%31==0)?(n/31 -1):floor(n/31)
for example: book in category_id = 100 like so:
bit code = (100%31) =7 <=>2^7 = 128,
group = floor(100%31) = 3 <=> in group_3
so if you need to query the entire book at category_id = 100, the query string is:
SELECT * FROM book WHERE group_3&128
Note: MySQL does not work with indexes if bitwise in where. But you can check from this link: bitwise operations and indexes
source to share