How do I write the insert sql statement for the following scenario ?
-
Hi expert, Database : MySQL I am trying to create 2 tables with a one-to-many relationship. Table 1 - tutor Table 2 - subject In my table tutor, I have id as the Primary key and it is auto-incremental. In my subject, I have id as the Primary key and it is auto-incremental, tutorNRIC as the foreign key and tutorNRIC in my tutor table as the reference key. For my subject, there will be more than one entry in the column tutor_subject. For example, a tutor will sign up and then all the subjects that she can teach will be entered into that column tutor_subjet. I'd like to check if my insert sql is correct :
private final String INSERT_QRY1 = "INSERT INTO hi5project.subject tutor_subject, tutorNRIC VALUES ('?', '?') SET tutorNRIC = hi5project.tutor.tutorNRIC";
Thanks.
For a start your table design is incorrect, you need a many to many table soa tutor can have mnay students and a student can have many tutors. What happens id=f a tutor leaves, or you have different tutors for different subjects? LinkID TutorID StudentID
Never underestimate the power of human stupidity RAH
-
For a start your table design is incorrect, you need a many to many table soa tutor can have mnay students and a student can have many tutors. What happens id=f a tutor leaves, or you have different tutors for different subjects? LinkID TutorID StudentID
Never underestimate the power of human stupidity RAH
-
Hello Mycroft Holmes, I am not creating a table to store the students. So, do I still need a many-to-many relationship between the table : tutor and the table : subject ?
Sorry my bad - but the same rule applies a tutor may have more than one subject and a subject may have more than one tutor. If these rules apply to your use case then you need a many to many table.
Never underestimate the power of human stupidity RAH
-
Sorry my bad - but the same rule applies a tutor may have more than one subject and a subject may have more than one tutor. If these rules apply to your use case then you need a many to many table.
Never underestimate the power of human stupidity RAH
OK. Tks. Can I know if the ammended database schema is correct ?
Create table tutor(
id integer, auto_increment;
tutorName varchar(50), not null,
tutorNRIC varchar(50), not null, (Primary Key)
tutorEmail varchar(50), not null,
);Create table subject(
id integer, auto_increment;
subject varchar950), not null;
subject_id, integer, not null, (Primary Key)
_;Create table tutor_subject(
tutorNRIC integer,
subject_id integer,
Primary Key(tutorNRIC, subject_id),
Foreign Key(tutorNRIC) references tutor(tutorNRIC),
Foreign Key(subject_id) references subject(subject_id)); -
OK. Tks. Can I know if the ammended database schema is correct ?
Create table tutor(
id integer, auto_increment;
tutorName varchar(50), not null,
tutorNRIC varchar(50), not null, (Primary Key)
tutorEmail varchar(50), not null,
);Create table subject(
id integer, auto_increment;
subject varchar950), not null;
subject_id, integer, not null, (Primary Key)
_;Create table tutor_subject(
tutorNRIC integer,
subject_id integer,
Primary Key(tutorNRIC, subject_id),
Foreign Key(tutorNRIC) references tutor(tutorNRIC),
Foreign Key(subject_id) references subject(subject_id));In the Tutor_Subject table I would use the Tutor ID field. The NRIC is a user input field and should not be used as a foreign key.
Never underestimate the power of human stupidity RAH
-
In the Tutor_Subject table I would use the Tutor ID field. The NRIC is a user input field and should not be used as a foreign key.
Never underestimate the power of human stupidity RAH
Hi Mycroft, Is it possible to use tutorNRIC as my primary key instead of tutorID ? my tutorID is just an index for me to keep track how many people have signed up. Another question is that how should I do my insert statement based on many to many relationship ?
-
Hi Mycroft, Is it possible to use tutorNRIC as my primary key instead of tutorID ? my tutorID is just an index for me to keep track how many people have signed up. Another question is that how should I do my insert statement based on many to many relationship ?
karengsh wrote:
Is it possible to use tutorNRIC as my primary key
Yes it is possible just wrong. A user should NEVER be able to edit a primary key.
karengsh wrote:
my tutorID is just an index for me to keep track how many people have signed up.
That statement means you need to do some reading/learning on database design. A primary key should be stupid and used for just one purpose, to identify the record, NOTHING else. Normally you would create the primary records and when you link them (via the UI) you create the link record with the 2 primary keys.
Never underestimate the power of human stupidity RAH
-
karengsh wrote:
Is it possible to use tutorNRIC as my primary key
Yes it is possible just wrong. A user should NEVER be able to edit a primary key.
karengsh wrote:
my tutorID is just an index for me to keep track how many people have signed up.
That statement means you need to do some reading/learning on database design. A primary key should be stupid and used for just one purpose, to identify the record, NOTHING else. Normally you would create the primary records and when you link them (via the UI) you create the link record with the 2 primary keys.
Never underestimate the power of human stupidity RAH
-
Hi Mycroft, The thing is that in my country NRIC is unique. Nobody holds the same NRIC. In this case, can I make it my Primary key ? And I'd like tutorID as my index with incremental value?
You are missing the point - NRIC is EDITABLE by the user therefore it should not be used as a primary key - you can put a unique constraint on it but do not use it as a foreign key in a related table.
karengsh wrote:
And I'd like tutorID as my index with incremental value
That is fine but it will not be a count of the records in the table.
Never underestimate the power of human stupidity RAH
-
You are missing the point - NRIC is EDITABLE by the user therefore it should not be used as a primary key - you can put a unique constraint on it but do not use it as a foreign key in a related table.
karengsh wrote:
And I'd like tutorID as my index with incremental value
That is fine but it will not be a count of the records in the table.
Never underestimate the power of human stupidity RAH