s****e
发帖数: 282 | 1 Design a table managing students and classes that a student takes.
There are two ways:
1. Using a vector as a column. The number of bits is equal to the number of
classes. "1" means enrolling in a class. "0" means not.
StudentID Class-vector
100 00110100
101 11010000
2. Use class name as a column.
StudentID ClassName
100 class3
100 class4
100 class6
101 class1
101 class2
101 class4
The number of class is not fixed. So |
B*****g
发帖数: 34098 | 2 2
Create 2 index
of
【在 s****e 的大作中提到】
: Design a table managing students and classes that a student takes.
: There are two ways:
: 1. Using a vector as a column. The number of bits is equal to the number of
: classes. "1" means enrolling in a class. "0" means not.
: StudentID Class-vector
: 100 00110100
: 101 11010000
: 2. Use class name as a column.
: StudentID ClassName
: 100 class3
|
s****e
发帖数: 282 | 3 Thanks.
How about the following two indexes:
1.
CREATE UNIQUE INDEX sid ON mytable (StudentID, ClassName)
This is for search like: SELECT * WHERE mytable.StudentID==100
2.
CREATE UNIQUE INDEX class ON mytable (ClassName, StudentID)
This is for search like: SELECT * WHERE mytable.ClassName==class2
【在 B*****g 的大作中提到】
: 2
: Create 2 index
:
: of
|
B*****g
发帖数: 34098 | 4 I think 1 is only good (compare to index(StudentID))for
SELECT StudentID, ClassName WHERE mytable.StudentID==100
But if you server is "strong", I think your design is good.
【在 s****e 的大作中提到】
: Thanks.
: How about the following two indexes:
: 1.
: CREATE UNIQUE INDEX sid ON mytable (StudentID, ClassName)
: This is for search like: SELECT * WHERE mytable.StudentID==100
: 2.
: CREATE UNIQUE INDEX class ON mytable (ClassName, StudentID)
: This is for search like: SELECT * WHERE mytable.ClassName==class2
|
n********6
发帖数: 1511 | 5 Yeah.
It's better to understand what are the most frequently used query.
If a lot of request for
SELECT StudentID, ClassName WHERE mytable.Student ID = 100
then it is good.
【在 B*****g 的大作中提到】
: I think 1 is only good (compare to index(StudentID))for
: SELECT StudentID, ClassName WHERE mytable.StudentID==100
: But if you server is "strong", I think your design is good.
|
j*****n
发帖数: 1781 | 6 figure out what is many-to-many relationship. |
s****e
发帖数: 282 | 7 If I create index only on the first column, like:
CREATE INDEX sid ON mytable (StudentID)
The index will not be an unique index. The following query will return many
rows, right?
SELECT StudentID, ClassName WHERE mytable.Student ID = 100
【在 n********6 的大作中提到】
: Yeah.
: It's better to understand what are the most frequently used query.
: If a lot of request for
: SELECT StudentID, ClassName WHERE mytable.Student ID = 100
: then it is good.
|
