List:General Discussion« Previous MessageNext Message »
From:Nils Valentin Date:June 2 2003 2:21am
Subject:Re: Inserting data?
View as plain text  
Hi Ted,

I believe that there is no easy right wrong answer here. Your question is to 
general to be ale to answer it correctly. The correct table format depends 
STRONGLY on how you want to use your data and which SELECT requests you will 
use.

It might make sense to put all data in 1 table in case you want to formulate a 
"simple" SELECT statement - however this will give you restrictions when the 
table grows -menaing you might have the same information which you need to 
update for originally a single record.

If you split your data so that you use f.e. 3 tables, making sure that each 
information is inserted only once than this is absolutely fine in the way of 
optimizing your data for the Normal forms (1-6), however this will make a 
SELECT statement more complicated as you will need to request the data from 
several tables. The advantae would be in case of updating any data record , 
you will have to update this only once, while in the previous sample you will 
have to update it several times (time consuming). While this is a complex 
topic I would suggest to read some articles from www.devshed.com about 
database normalization


Best regards

Nils Valentin
Tokyo/Japan
 


2003年 6月 2日 月曜日 07:32、Becoming Digital さんは書きました:
> > 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
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
> http://lists.mysql.com/mysql?unsub=1

-- 
================================================
Valentin Nils
Internet Technology

 E-Mail: nils@stripped
 URL: http://www.knowd.co.jp
================================================

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