|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
ok. Tks. Mycroft Holmes.
However, how do I insert into the third table?
Should I use select subj_Id from subject and select tutor_id and then insert into the 3rd table ?
|
|
|
|
|
|
Your question is not very clear.
Are you asking why we use datareader.
The following link might be a good read for you
DataSet vs. DataReader[^]
|
|
|
|
|
I think he just wanted to say hello...
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I am trying to run an SSIS Package which is using Oracle Db as Datasource. When I am running the Package I am getting the above error: attempt to load oracle client libraries threw badimageformatexception ssis. I have downloaded the Oracle Client tools as suggested in a link upon goolging. Still I am not able to find an answer for it. Any help can be greatly helpful.
As that Database is secured, I am encrypting the connection string and placing it into a variable, then assigning to ConnectionString property the Source Connection, could it be the problem? I am not understanding what am I missing here.
Any help would b very helpful thanks in advance.
Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
-- modified 1-Jun-17 20:13pm.
|
|
|
|
|
A "bad image format" exception usually points to trying to load 64-bit libraries in a 32-bit AppDomain, or vice-versa.
You'll need to check whether your package is running as 32-bit or 64-bit, and make sure you're loading the correct version of the Oracle libraries.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I'm using ADO.NET.
Entity Framework is not an option.
Suppose I have an IDataReader instance that was created from calling a stored procedure that returns 150,000 rows.
If I create a DataTable, and then call DataTable.Load(IDataReader), does it:
1. Read all available rows into the table at once?
2. Read only those rows that are necessary to return whatever rows I read from the DataTable?
3. Some hybrid of the above two scenarios?
I'm interested in implementing a solution that only reads data from the server upon demand.
IOW, I don't want my form to only appear on screen after all 150,000 rows have been pulled from SQL Server.
Thank you very much for your insight.
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
#1 - it loads all of the rows into memory at once. DataTable.Load | Reference Source[^]
Displaying 150,000 rows on screen in one hit is not a good idea. How is the poor user ever going to find the one row they're interested in? There must be some way to summarise, filter, or page the data to make it easier to consume.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanks for your response. I only chose 150,000 as a theoretical number to make my point.
There would never be that many rows, but in our main LOB application, some developers in the past have loaded up around 10,000 rows, making the form rather slow to open!
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
I agree with Richard, 150k records to the UI is only valid if you are doing a data dump and there are more efficient way to do that.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yes, I agree with you and Richard. I'm working on creating a generic way to load the data only on demand. It must be a business object agnostic way.
Thanks. 
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
If you are using SQL Server I think there is a method of loading a "page" of data which my be valid. Obviously I have never used it, I limit UI lists to a max of 2k. After that filters become mandatory.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
We have one form that displays Purchase Orders, and the data goes back to 2009. There are thousands upon thousands of records.
How can you limit how many rows you load, and yet still allow the user to type in a column filter string in order to find any of the PO's from any point in time?
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
Richard Andrew x64 wrote: IOW, I don't want my form to only appear on screen after all 150,000 rows have been pulled from SQL Server.
Then how?
You mentioned a "form", so I'd assume WinForms with a DataGridView. In which case you don't use a datatable, but a list. You add to the list from a different thread, in batches of 50. Virtualize the DataGridView. Start the actions as soon as the form is displayed.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Eddy Vluggen wrote: You add to the list from a different thread, in batches of 50
Thank you. That's one of the avenues I am exploring. I have to find some way to make our application more responsive while still enabling access to thousands of rows.
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
