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: <[EMAIL PROTECTED]> > To: "Becoming Digital" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > 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: <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > 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/[EMAIL PROTECTED] -- ================================================ Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp ================================================ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
