On Wednesday, January 9, 2002, at 03:06 PM, Rick Emery wrote:
> First, you are describing a "one-to-many" relationship, bceasue one > "file" > record can be referenced by many "people" records. If this is the > case, you > may wish to re-design your tablse such that a "people record" contains a > "file_id" field. You can then do away with the "filespeople" table > altogether. Normalization is a good thing; but not when it is at the > detriment of good design including how one processes it. In this one example, it's true that it's "one-to-many". But in the database overall, there can be any number of files that correspond to any number of people. In other words: Joe, Lisa, Ryan, and Josh worked on File 1, File 2, and File 3 Gino only worked on File 1 and File 4 but Lisa helped him on File 4 etc so, one person can have many files associated with them, and one file can have many people associated with them. Which is why I constructed this with the foreign key. I think it would be a lot easier if I could make it "one-to-many"!! In any event, there are other relationships in the database that are also many-to-many, so I am curious what is the standard way to go about entering data into tables in a way that keeps them connected. > If you do require the "filespeople" table, then you'll have to INSERT > records programmatically with your favorite scripting (PHP,ASP,PERL) > language or program language. > > FYI. Your "filespeople" file indicates that both fields are PRIMARY > keys. > That cannot be. Only one field may be PRIMARY. I just now tried it to > be > certain. Yeah, this is the way that "mysqlshow" outputs the data. In reality, each column is a UNIQUE index (-not- a PRIMARY KEY). For some reason, that's what mysqlshow gives instead of UNIQUE. Thanks for the input, though! Erik > > -----Original Message----- > From: Erik Price [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, January 09, 2002 11:14 AM > To: [EMAIL PROTECTED] > Cc: Etienne Marcotte > Subject: INSERTing into joined tables? > > > Hello, everyone -- > I have received a great deal of help from many members of this list, so > I'd like to acknowledge that now. Someday, when I have my head wrapped > around this stuff, I hope to return the favor. In the meantime, > however, I have come across my worst brainbuster yet. Any help on this > is greatly appreciated. > > > I constructed my tables in the most "normalized" way that I could > (without overdoing it), so that in some cases, there is no -direct- > relationship between tables. That is, I have a table called "people" > and a table called "files", and a foreign key table called > "filespeople": > > Database: medialab_db Table: people > +-------------+----------------------+------+-----+ > | Field | Type | Null | Key | > +-------------+----------------------+------+-----+ > | people_id | smallint(5) unsigned | | PRI | (auto-incremented) > | first_name | varchar(36) | | | > | last_name | varchar(36) | | | > +-------------+----------------------+------+-----+ > > Database: medialab_db Table: files > +-------------+-----------------------+------+-----+ > | Field | Type | Null | Key | > +-------------+-----------------------+------+-----+ > | file_id | mediumint(8) unsigned | | PRI | (auto-incremented) > | file_name | varchar(64) | | | > +-------------+-----------------------+------+-----+ > > Database: medialab_db Table: filespeople > +-----------+-----------------------+------+-----+ > | Field | Type | Null | Key | > +-----------+-----------------------+------+-----+ > | file_id | mediumint(8) unsigned | | PRI | > | people_id | smallint(5) unsigned | | PRI | > +-----------+-----------------------+------+-----+ > > The relationship, in real life, is that I would like to establish > many-to-many relationships between "files" records and "people" records, > so that a record in "files" would be associated with several people from > "people". There is a foreign key table called "filespeople". The SQL > used to write a SELECT statement would use the join like so: > > SELECT files.file_name, people.first_name, people.last_name > FROM files, people, filespeople > WHERE files.file_name = $filename > AND files.file_id = filespeople.file_id > AND people.people_id = filespeople.people_id > (the $filename variable is a user-selected variable, I'm using PHP) > > So I designed my "files" and "people" tables without any direct > relationship with one another, thinking to link them with the SELECT > statement. > > What I completely forgot, up until this point, was that I would need to > INSERT these records (from pre-written HTML/PHP forms), and there is no > WHERE clause in the INSERT statement to keep everything together. In my > scenario, a user might add a record to "files" and wish to associate > that record to some of the records in "people", either new or > pre-existing (typed into an HTML text input form or something). How > should SQL code be arranged to "link" these records over the foreign key > table? > > INSERT INTO files (file_name) VALUES ("$filename") > > and > > INSERT INTO people (first_name, last_name) VALUES ("$firstname", > "$lastname") > > but... to keep it all together... is lost on me... and then later to > have UPDATE statements to do the same thing! Although I suspect this > may be easier as I can use the WHERE clause in an UPDATE statement. > > If anyone has a link to a tutorial on this very concept, that would be > greatly appreciated as well! > > > Thank you, > Erik > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <mysql-unsubscribe- > [EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
