List:General Discussion« Previous MessageNext Message »
From:trogers Date:June 2 2003 1:48am
Subject:Re: becoming unconfused
View as plain text  
I just came from the bookstore where I perused "Mastering MySQL 4".  
Specifically, I wanted to know how to specify a Foreign Key.  After a 
thorough, albeit short lived search, and reading every page the index 
on "Foreign Key" pointed to, I deduced that all one needs do is put the 
blasted field (primary key from other table) into the "many" (re 
:appropriate) table.  That there was no "identifier" like there is a 
Primary Key.

Ok, my last puzzlement --for now-- is, let me illustrate:

table1				table2
studentID -->PK      classID -->PK
student_name         class_name
                                   studentID  (foreign key)

Simple (yes I'm slow, yes it's a problem) question:  how does the 
studentID field in table2 get populated?

Thanks in Advance,
Ted


On Sunday, June 1, 2003, at 06:32 PM, Becoming Digital wrote:

>> The way I was taught, in my limited education, was simply to have, 
>> say,
>> the people_id also appear in table2 as a Foreign Key, that tis would
>> serve to relate the tables.  Now I've been advised that the way to do
>> this is NOT like that, but to have a third, linking, table
>
> In my opinion, you've been wrongly advised.  I would follow your 
> original
> instinct and use a one->many relationship for people to machines.  
> Using a third
> table is just going to complicate things and goes against the concepts 
> of
> normalized data.
>
> Edward Dudlik
> Becoming Digital
> www.becomingdigital.com
>
>
> ----- Original Message -----
> From: <trogers@stripped>
> To: "Becoming Digital" <subs@stripped>
> Cc: <mysql@stripped>
> Sent: Sunday, 01 June, 2003 15:59
> Subject: Re: Inserting data?
>
>
> I'd be glad to share.. you mean what the db is supposed to be?  Ok,
> there are 3 tables:
> table1 = people, people_id = primary key
> table2 = machines, machines_id = primary key
> One person can have many machines, one machine can only be assigned to
> one person.
> The way I was taught, in my limited education, was simply to have, say,
> the people_id also appear in table2 as a Foreign Key, that tis would
> serve to relate the tables.  Now I've been advised that the way to do
> this is NOT like that, but to have a third, linking, table where there
> are 2 fields: one is the people_id (primary key from table1) and the
> other is the machines_id (primary key from table2).
>
> I was asking how to write a SQL statement to populate that third table
> from the data already in/from the other 2.
>
> Any other comments about this are welcome.
>
> Thanks,
> Ted
>
> On Sunday, June 1, 2003, at 10:53 AM, Becoming Digital wrote:
>
>> You need a constraint on the data to be inserted into the second
>> table.  Care to
>> share what that is supposed to be?
>>
>> Edward Dudlik
>> Becoming Digital
>> www.becomingdigital.com
>>
>>
>> ----- Original Message -----
>> From: <trogers@stripped>
>> To: <mysql@stripped>
>> Sent: Sunday, 01 June, 2003 03:17
>> Subject: Inserting data?
>>
>>
>> I have a table, in the table is a field called  name_id; in a second
>> table (a linking table) I also have the field name_id, this should be
>> the same/reference the same name_id as in the first table.
>>
>> The first table is fully populated.  How do write a SQL statement to
>> get the data from the name_id field of table1 into the name_id field 
>> of
>> table2?
>>
>> (I'll do this and find out after if it actually references the same
>> data!  So, if it doesn't at least I'll have learned the proper SQL
>> statement. :)
>>
>> Thank you,
>> Ted Rogers

Thread
indexes question(Daniel Rossi)1 Jun
  • Re: indexes questionDan Nelson1 Jun
    • Re: indexes questiondaniel1 Jun
      • Re: indexes questionDan Nelson1 Jun
  • Inserting data?trogers1 Jun
    • Re: Inserting data?Becoming Digital1 Jun
      • Re: Inserting data?trogers1 Jun
        • Re: Inserting data?Becoming Digital2 Jun
          • Re: becoming unconfusedtrogers2 Jun
            • Re: becoming unconfusedBecoming Digital2 Jun
              • Re: becoming unconfusedtrogers2 Jun
              • Re: becoming unconfusedtrogers2 Jun
          • Re: Inserting data?Nils Valentin2 Jun